How to use a table variable in a dynamic query

Question

I want to use a table variable instead of a Temp table, but my main query construct is dynamic. A dynamic query consists of a single quote, as I can get data from @TableVariable. I do not want to delete the dynamic query, because some parameters will be added later.

eg. The following error code is where I wrote a table variable in 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 with this.

Thanks at Advance.

Answer 1

Try the following:

 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; 

Answer 2

As an alternative solution, you can use #TempVehicles instead of storing it in a 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 checking if it meets your requirements.

Answer 3

You must contain a table variable definition in a dynamic query. so the final request will look like

 DECLARE @SQL NVARCHAR(MAX) SET @SQL = 'DECLARE @TempVehicles TABLE(VehicleID INT ) ' SET @SQL = @SQL + 'INSERT INTO @TempVehicles([VehicleID]) SELECT VehicleID FROM tbl_Vehicles ' SET @SQL = @SQL + 'SELECT Cust_ID,A.VehicleID,GISInfo FROM @TempVehicles A INNER JOIN tbl_GISData B ON A.VehicleID=B.VehicleID' EXECUTE SP_EXECUTESQL @SQL 

The table variable is not available outside the dynamic SQL query statement @SQL