using Table variable with sp_executesql

by

Here’s an example of how to pass a table-valued parameter to sp_executesql. The variable has to be passed readonly:

if exists (select * from sys.types where name="TestTableType")
    drop type TestTableType

create type TestTableType as table (id int)
go
declare @t TestTableType
insert @t select 6*7

exec sp_executesql N'select * from @var', N'@var TestTableType readonly', @t

This prints the Answer to the Ultimate Question of Life, the Universe, and Everything.

Leave a Comment