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