7

I want to use Table Variable instead of Temp Table but My main query construction is Dynamic . Dynamic query is in single quote so how can I fetch data from @TableVariable. I dont want to remove dynamic query because some of parameter will added later.

e.g. following is error code , where I have wrote table variable into Dynamic Query........

/*Declare Table Variable*/
DECLARE @TempVehicles TABLE
    (
        [VehicleID] INT
    )   
/*Insert data into Table Variable*/
INSERT INTO @TempVehicles
(
    [VehicleID]
)
SELECT  VehicleID
FROM tbl_Vehicles

/*Dynamic Query and Main SQL Construction*/
DECLARE @SQL NVARCHAR(MAX)
SET @SQL ='SELECT Cust_ID,A.VehicleID,GISInfo 
           FROM @TempVehicles A INNER JOIN tbl_GISData B ON A.VehicleID=B.VehicleID'

EXECUTE SP_EXECUTESQL @SQL

Help me , in this .

Thanks in Advance.

Ingo Karkat
  • 167,457
  • 16
  • 250
  • 324
Anvesh
  • 7,103
  • 3
  • 45
  • 43

2 Answers2

11

Try This :

CREATE TYPE IntegerTableType AS TABLE (ID INT);
go

DECLARE @TempVehicles IntegerTableType; 

INSERT  @TempVehicles
values (1);





DECLARE @SQL NVARCHAR(MAX);
SET @SQL ='SELECT * 
           FROM @TempVehicles;';


EXECUTE SP_EXECUTESQL @SQL,N'@TempVehicles IntegerTableType READONLY',
@TempVehicles;
Ravi Singh
  • 2,042
  • 13
  • 29
4

As an alternate solution you can use #TempVehicles instead of storing it in variable. 

SELECT  VehicleID 
    into #TempVehicles
FROM tbl_Vehicles

/*Dynamic Query and Main SQL Construction*/
DECLARE @SQL NVARCHAR(MAX)
SET @SQL ='SELECT Cust_ID,A.VehicleID, GISInfo 
           FROM #TempVehicles A INNER JOIN tbl_GISData B ON A.VehicleID=B.VehicleID; 
           Drop Table #TempVehicles'

EXECUTE SP_EXECUTESQL @SQL

Try and check whether it meets your requirement.

Vishal Vaishya
  • 596
  • 3
  • 15