Passing user defined table parameter to dynamic sql, sp_executesql

Question

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.

Folkway

Answer 1

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

Daveen

I'm sorry, I forgot to write that when I'm writing the question here. The query already has it. but it gives the same error. – Atherosclerosis

Added a working example. Check the compatiblity level for your database, from select name, compatibility_level from sys.databases ? It should be set to 90 or higher, for SQL Server 2005+. – Daveen

The example I posted runs fine on my installation, so I'm sure you can pass table-valued parameters using sp_executesql – Daveen

Hi, sp_executesql is making my dynamic query very slow, If I dry-run the same query it give the result in 1 seconds. Could you please suggest how to fix this issue. – Bates