6

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.

Brian Tompsett - 汤莱恩
  • 5,195
  • 62
  • 50
  • 120
rockin'
  • 699
  • 3
  • 7
  • 16

1 Answers1

8

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
Andomar
  • 216,619
  • 41
  • 352
  • 379
  • 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. – rockin' Nov 03 '11 at 18:05
  • 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+. – Andomar Nov 03 '11 at 18:12
  • The example I posted runs fine on my installation, so I'm sure you can pass table-valued parameters using `sp_executesql` – Andomar Nov 03 '11 at 18:30
  • that's amazing, it works now. thank you very much for your help. – rockin' Nov 03 '11 at 18:38