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.
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