SQL 如何在动态查询中使用表变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16393168/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use Table Variable in Dynamic Query
提问by Anvesh
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.
我想使用 Table Variable 而不是 Temp Table 但我的主要查询构造是 Dynamic 。动态查询是单引号，所以我如何从@TableVariable 中获取数据。我不想删除动态查询，因为稍后会添加一些参数。
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.
提前致谢。
回答by Ravi Singh
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;
回答by Vishal Vaishya
As an alternate solution you can use #TempVehicles instead of storing it in variable.
作为替代解决方案，您可以使用 #TempVehicles 而不是将其存储在变量中。
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.
尝试检查它是否符合您的要求。
回答by Mudassir Hasan
You have to contain table variable definition within dynamic query . so final query would be 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 accessible outside the dynamic SQL query statement @SQL
表变量在动态SQL查询语句@SQL之外不可访问
