Using Table Variable with Dynamic Queries in SQL Server 2008

Posted by PandianS under Error and Solution on 7/7/2010 | Views : 33321 | Status : [Member] [MVP] | Replies : 1

Creating Sample Table:
If OBJECT_ID('Tb_DotnetFunda','U') Is not Null
Drop Table Tb_DotnetFunda
Go
Create Table Tb_DotnetFunda
(
ID Int Identity(1,1),
Column1 Varchar(50)
)
Go

Inserting Sample Records:
Insert Tb_DotnetFunda(Column1) Values('Aequea')
Insert Tb_DotnetFunda(Column1) Values('Salino')
Insert Tb_DotnetFunda(Column1) Values('Calcalino')
Insert Tb_DotnetFunda(Column1) Values('Setaceo')
Insert Tb_DotnetFunda(Column1) Values('Socaprio')
Insert Tb_DotnetFunda(Column1) Values('Alumino')
Insert Tb_DotnetFunda(Column1) Values('Vitriolic')
Go

Fetching all records:
Select * from Tb_DotnetFunda
Go
ID	Column1
1 Aequea
2 Salino
3 Calcalino
4 Setaceo
5 Socaprio
6 Alumino
7 Vitriolic


Fetching dynamic records using dynamic query:
Declare @Nsql	NVARCHAR(100)

Declare @IDs Table(ID Int)
Insert @IDs Values(2)
Insert @IDs Values(4)
Insert @IDs Values(6)

Select @Nsql = N'Select ID,Column1 From Tb_DotnetFunda Where Id in(Select Id From @L_IDs)'

Exec SP_ExecuteSQL @Nsql,N'@L_IDs Table(ID Int)',@L_IDs = @IDs


Normally, It should work. But here NOT. It is throwing Err
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 "@L_IDs".


Solution:
Normally, We need Physical type like Table Valued Parameters, then only we can use Table variable inside the Dynamic queries.

Step:1 , Creating User defined Table Type

Create Type IntergerType As Table(ID Int)


Step:2 , Declaring Table variable using the User defined Table valued Type just we created and Inserting Data.

Declare @IDs IntergerType

Insert @IDs Values(2)
Insert @IDs Values(4)
Insert @IDs Values(6)


Step:3 , Using this Table variable inside the Dynamic queries.

Declare @Nsql  NVARCHAR(100)

Select @Nsql = N'Select ID,Column1 From Tb_DotnetFunda Where Id in(Select Id From @L_IDs)'

Exec SP_ExecuteSQL @Nsql,N'@L_IDs IntergerType Readonly',@L_IDs = @IDs


Result:
ID	Column1
2 Salino
4 Setaceo
6 Alumino


Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions



Responses

Posted by: Vuyiswamb on: 7/31/2010 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
This is very good

Thank you for posting at Dotnetfunda
[Administrator]

PandianS, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response