SQL 将 TABLE 变量传递给 sp_executesql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4258798/
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
Pass a TABLE variable to sp_executesql
提问by Alex
I'm trying to pass a TABLE variable to the sp_executesql procedure:
我正在尝试将 TABLE 变量传递给 sp_executesql 过程：
DECLARE @params NVARCHAR(MAX)
SET @params = '@workingData TABLE ( col1 VARCHAR(20),
col2 VARCHAR(50) )'
EXEC sp_executesql @sql, @params, @workingData
I get the error:
我收到错误：
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TABLE'.
I tried omitting the column specification after 'TABLE'. I also tried to declare the table as a variable inside the dynamic SQL. But no luck...
我尝试在“TABLE”之后省略列规范。我还尝试将该表声明为动态 SQL 中的变量。但没有运气...
Seems to me that TABLE variables aren't allowed to be passed as parameters in this procedure?. BTW: I'm running MSSQL2008 R2.
在我看来，在此过程中不允许将 TABLE 变量作为参数传递？。顺便说一句：我正在运行 MSSQL2008 R2。
I'm not interested in using a local temp table like #workingData because I load the working data from another procedure:
我对使用像 #workingData 这样的本地临时表不感兴趣，因为我从另一个过程加载工作数据：
INSERT INTO @workingData
EXEC myProc @param1, @param2
Which I cannot do directly into a temp varaible (right?)...
我不能直接进入临时变量（对吗？）...
Any help appreciated!
任何帮助表示赞赏！
回答by Gary Kindel
If you are using SQL Server 2008, to pass a table variable to a stored procedure you must first define the table type, e.g.:
如果您使用的是 SQL Server 2008，要将表变量传递给存储过程，您必须首先定义表类型，例如：
CREATE TYPE SalesHistoryTableType AS TABLE
(
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
GO
or use an existing table type stored in the database.
或使用存储在数据库中的现有表类型。
Use this query to locate existing table types
使用此查询来定位现有的表类型
SELECT * FROM sys.table_types
To use in an stored procedure, declare an input variable to be the table:
要在存储过程中使用，请将输入变量声明为表：
CREATE PROCEDURE usp_myproc
(
@TableVariable SalesHistoryTableType READONLY
)
AS BEGIN
--Do stuff
END
GO
Populate the table variable before passing to the stored procedure:
在传递给存储过程之前填充表变量：
DECLARE @DataTable AS SalesHistoryTableType
INSERT INTO @DataTable
SELECT * FROM (Some data)
Call the stored procedure:
调用存储过程：
EXECUTE usp_myproc
@TableVariable = @DataTable
Further discussions here.
进一步的讨论在这里。
回答by Alex
OK, this will get me what I want, but surely isn't pretty:
好的，这会让我得到我想要的，但肯定不漂亮：
DECLARE @workingData TABLE ( col1 VARCHAR(20),
col2 VARCHAR(20) )
INSERT INTO @workingData
EXEC myProc
/* Unfortunately table variables are outside scope
for the dynamic SQL later run. We copy the
table to a temp table.
The table variable is needed to extract data directly
from the strored procedure call above...
*/
SELECT *
INTO #workingData
FROM @workingData
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT * FROM #workingData'
EXEC sp_executesql @sql
There must be a better way to pass this temporary resultset into sp_executesql!?
一定有更好的方法将这个临时结果集传递给 sp_executesql！？
Regards Alex
问候亚历克斯
回答by Tim Friesen
While this may not directly answer your question, it should solve your issue overall.
虽然这可能不能直接回答您的问题，但它应该可以解决您的整体问题。
You can indeed capture the results of a Stored Procedure execution into a temporary table:
您确实可以将存储过程执行的结果捕获到临时表中：
INSERT INTO #workingData
EXEC myProc
So change your code to look like the following:
因此，将您的代码更改为如下所示：
CREATE TABLE #workingData ( col1 VARCHAR(20),
col2 VARCHAR(20) )
INSERT INTO #workingData
EXEC myProc
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT * FROM #workingData'
EXEC sp_executesql @sql
Regards, Tim
问候， 蒂姆
回答by kta
Alter PROCEDURE sp_table_getcount
@tblname nvarchar(50) ,
@totalrow int output
AS
BEGIN
Declare @params nvarchar(1000)
Declare @sql nvarchar(1000)
set @sql = N'Select @cnt= count(*) From @tbl'
set @params = N'@tbl nvarchar(50) , @cnt int OUTPUT'
Exec sp_executesql @sql , @params ,@tbl=@tblname , @cnt = @totalrow OUTPUT
END
GO
Please note that the above code will not work as table as a object is out of the scope.It will give you the error: must declare table variable.In order to work around we can do the following.
请注意，由于对象超出范围，上面的代码将无法作为表工作。它会给你错误：必须声明表变量。为了解决这个问题，我们可以执行以下操作。
Alter PROCEDURE sp_table_getcount
@tblname nvarchar(50) ,
@totalrow int output
AS
BEGIN
Declare @params nvarchar(1000)
Declare @sql nvarchar(1000)
set @sql = N'Select @cnt= count(*) From dbo.' + @tblname
set @params = N'@cnt int OUTPUT'
Exec sp_executesql @sql , @params , @cnt = @totalrow OUTPUT
END
GO
