Menu
I need help with passing my "user defined table type" parameter to dynamic sql, sp_executesql.
Here's my sample code:
DECLARE @str as nvarchar(Max)
DECLARE @IDLIST AS ListBigintType /* this is my table type, with ItemId column (bigint)*/
INSERT INTO @IDLIST
SELECT DISTINCT bigintid FROM tableWithBigInts WITH(NOLOCK)
set @str ='select * from SomeTable where ID in (select ItemId from @IdTable) '
EXEC sp_executesql @str , @ParamDefs, @IdTable = @IDLIST
It says : Must declare the table variable "@IdTable"
I can't get this to work, and can't get a workaround with coalesce (for bigints) either because the result will be more than 8000 characters.
989
Executing dynamic SQL queries Dynamic SQL queries are those built at runtime based on one or more variable values. To execute those queries, we must concatenate them into one SQL statement and pass them as a parameter to the sp_executesql stored procedure.
You can use a table variable with dynamic SQL, but you must declare the table inside the dynamic SQL itself.
The sp_executesql is a built-in stored procedure in SQL Server that enables to execute of the dynamically constructed SQL statements or batches. Executing the dynamically constructed SQL batches is a technique used to overcome different issues in SQL programming sometimes.
Using CTEs, for instance, you can use SELECT from <subquery> in Open SQL. In my case I needed to execute dynamic SELECT count( DISTINCT col1, col2, …) which is not possible in the regular OpenSQL.
Try setting @ParamDefs to:
EXEC sp_executesql @str , N'@IdTable ListBigintType readonly', @IdTable = @IDLIST
Here's a full working example:
create type ListBigintType as table (ItemId bigint)
go
declare @t as ListBigintType
insert @t select 6*7
exec sp_executesql
N'select ItemId from @IdTable',
N'@IdTable ListBigintType readonly', @t
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
