Friday 9 March 2012

Table Valued Parameter

There was  a situation where we wanted to use table variable in dynamic sql join statements. However attempting to write below code deesnt works:

declare  @sql as nvarchar(1000)

declare  @tablevar as table(name nvarchar(10),age int)

insert into @tablevar values ('johny',66)

exec sp_executesql N'select * from @tablevar', N'@tablevar table', @tablevar = @tablevar

Above statement will throw following Error Message:
--Msg 156, Level 15, State 1, Line 1
--Incorrect syntax near the keyword 'table'.
--Msg 1087, Level 15, State 2, Line 1
--Must declare the table variable "@tablevar"

In SQL Server 2008 there is a new feature of user defined table types. We can create a table type of the the desired schema and then declare a variable of that type. Variable created of type user defined table types are called table valued parameters (TVP). TVP parameters could be passed to stored procedures and functions. These variable can also be used in dynamic sql statements.

create type tabletype as table(name nvarchar(10),age int)

declare @tablevariable as tabletype

insert into @tablevariable values ('poonam',66)

exec sp_executesql N'select * from @tablevariable',N'@tablevariable tabletype
READONLY',@tablevariable = @tablevariable

Please note the usage of "READONLY" option which is compulosry while passing a table valued parameter as a parameter.

Reference URL: http://msdn.microsoft.com/en-us/library/bb510489%28v=sql.100%29.aspx

No comments:

Post a Comment