Unable to use TVP as query input

[CDoughty08]

Providing Table Value Parameter as input to query fails on sp_executesql

Expected behaviour:

The query to execute successfully and give the result of

Actual behaviour:

The query fails to execute due to an issue with sp_executesql

Example that works in raw tsql

CREATE TYPE MyAwesomeType AS TABLE (storeNbr INT);

DECLARE @tvp StoreNumberTableType
INSERT INTO @tvp
VALUES (1), (2)

exec sp_executesql N'SELECT * FROM @tvp', N'@tvp MyAwesomeType readonly', @tvp

Translated example that fails in node-mssql

const t = new sql.Table('MyAwesomeType');

t.columns.add('storeNbr', sql.Int);
t.rows.add(1);
t.rows.add(2);

const req = connection.request();

req.input('tvp', t);
return req.query(`SELECT * from @tvp`);

Configuration:

// paste relevant config here

Software versions

• NodeJS: 12.13.1
• node-mssql: 6.2.1
• SQL Server: Enterprise 2017

[dhensby]

I'm actually not a big expert of TVPs, what is the error that is given? Have you done any debugging to see why it's not working as expected?

[lostfields]

I'm having trouble with this as well, and when above code is executed it returns the error The procedure "sp_executesql" has no parameter named "@tvp"

[dhensby]

It seems you need to be using a stored procedure and not just a raw query for TVP to work, see https://github.com/tediousjs/node-mssql#table-valued-parameter-tvp

[CDoughty08]

Hmm, would it be a reasonable request to get this fixed to work with raw queries since it is technically valid SQL for SQL Server? Falling back to stored procedures would introduce a lot of bloat for things that do not really benefit from stored procedures.

[dhensby]

It's not something on the roadmap, but PRs are welcome to add this feature

[CDoughty08]

I will look into it and see if I can figure it out to submit a PR. Thank you.

[CDoughty08]

@dhensby Looks like this is an issue with the way the underlying tediousjs library handles executing raw queries. Will close this issue and continue looking into this issue at the tedious layer. Will link the PR here, if I am able to figure out an acceptable fix.