SQL 如何在动态sql语句中使用表变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4626292/
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 a dynamic sql statement?
提问by Ashar Syed
In my stored procedure I declared two table variables on top of my procedure. Now I am trying to use that table variable within a dynamic sql statement but I get this error at the time of execution of that procedure. I am using Sql Server 2008.
在我的存储过程中，我在我的过程之上声明了两个表变量。现在我试图在动态 sql 语句中使用该表变量，但在执行该过程时出现此错误。我正在使用 SQL Server 2008。
This is how my query looks like,
这就是我的查询的样子，
set @col_name = 'Assoc_Item_'
+ Convert(nvarchar(2), @curr_row1);
set @sqlstat = 'update @RelPro set '
+ @col_name
+ ' = (Select relsku From @TSku Where tid = '
+ Convert(nvarchar(2), @curr_row1) + ') Where RowID = '
+ Convert(nvarchar(2), @curr_row);
Exec(@sqlstat);
And I get the following errors,
我收到以下错误，
Must declare the table variable "@RelPro". Must declare the table variable "@TSku".
必须声明表变量“@RelPro”。必须声明表变量“@TSku”。
I have tried to take the table outside of the string block of dynamic query but to no avail.
我试图将表移到动态查询的字符串块之外，但无济于事。
采纳答案by Joe Stefanelli
Your EXEC executes in a different context, therefore it is not aware of any variables that have been declared in your original context. You should be able to use a temp table instead of a table variable as shown in the simple demo below.
您的 EXEC 在不同的上下文中执行，因此它不知道在您的原始上下文中声明的任何变量。您应该能够使用临时表而不是表变量，如下面的简单演示所示。
create table #t (id int)
declare @value nchar(1)
set @value = N'1'
declare @sql nvarchar(max)
set @sql = N'insert into #t (id) values (' + @value + N')'
exec (@sql)
select * from #t
drop table #t
回答by Martin Smith
On SQL Server 2008+ it is possible to use Table Valued Parameters to pass in a table variable to a dynamic SQL statement as long as you don't need to update the values in the table itself.
在 SQL Server 2008+ 上，只要您不需要更新表本身中的值，就可以使用表值参数将表变量传递给动态 SQL 语句。
So from the code you posted you could use this approach for @TSkubut not for @RelPro
因此，从您发布的代码中，您可以将这种方法用于@TSku但不能用于@RelPro
Example syntax below.
下面的示例语法。
CREATE TYPE MyTable AS TABLE
(
Foo int,
Bar int
);
GO
DECLARE @T AS MyTable;
INSERT INTO @T VALUES (1,2), (2,3)
SELECT *,
sys.fn_PhysLocFormatter(%%physloc%%) AS [physloc]
FROM @T
EXEC sp_executesql
N'SELECT *,
sys.fn_PhysLocFormatter(%%physloc%%) AS [physloc]
FROM @T',
N'@T MyTable READONLY',
@T=@T
The physloccolumn is included just to demonstrate that the table variable referenced in the child scope is definitely the same one as the outer scope rather than a copy.
physloc包含该列只是为了证明在子作用域中引用的表变量绝对与外部作用域相同，而不是副本。
回答by gbn
You don't haveto use dynamic SQL
你不具备使用动态SQL
update
R
set
Assoc_Item_1 = CASE WHEN @curr_row = 1 THEN foo.relsku ELSE Assoc_Item_1 END,
Assoc_Item_2 = CASE WHEN @curr_row = 2 THEN foo.relsku ELSE Assoc_Item_2 END,
Assoc_Item_3 = CASE WHEN @curr_row = 3 THEN foo.relsku ELSE Assoc_Item_3 END,
Assoc_Item_4 = CASE WHEN @curr_row = 4 THEN foo.relsku ELSE Assoc_Item_4 END,
Assoc_Item_5 = CASE WHEN @curr_row = 5 THEN foo.relsku ELSE Assoc_Item_5 END,
...
from
(Select relsku From @TSku Where tid = @curr_row1) foo
CROSS JOIN
@RelPro R
Where
R.RowID = @curr_row;
回答by codingbadger
You can't do this because the table variables are out of scope.
您不能这样做，因为表变量超出了范围。
You would have to declare the table variable inside the dynamic SQL statement or create temporary tables.
您必须在动态 SQL 语句中声明表变量或创建临时表。
I would suggest you read this excellent article on dynamic SQL.
我建议您阅读这篇关于动态 SQL 的优秀文章。
回答by Dr. Wily's Apprentice
I don't think that is possible (though refer to the update below); as far as I know a table variableonly exists within the scope that declared it. You can, however, use a temp table(use the create tablesyntax and prefix your table name with the # symbol), and that will be accessible within both the scope that creates it and the scope of your dynamic statement.
我认为这是不可能的（尽管请参阅下面的更新）；据我所知，表变量只存在于声明它的范围内。但是，您可以使用临时表（使用create table语法并在表名前加上 # 符号），并且可以在创建它的范围和动态语句的范围内访问该表。
UPDATE: Refer to Martin Smith's answer for how to use a table-valued parameter to pass a table variable in to a dynamic SQL statement. Also note the limitation mentioned: table-valued parameters are read-only.
更新：有关如何使用表值参数将表变量传递给动态 SQL 语句的信息，请参阅 Martin Smith 的回答。还要注意提到的限制：表值参数是只读的。
回答by Ashar Syed
Well, I figured out the way and thought to share with the people out there who might run into the same problem.
好吧，我找到了方法并想与可能遇到相同问题的人分享。
Let me start with the problem I had been facing,
让我从我一直面临的问题开始，
I had been trying to execute a Dynamic Sql Statement that used two temporary tables I declared at the top of my stored procedure, but because that dynamic sql statment created a new scope, I couldn't use the temporary tables.
我一直在尝试执行一个动态 Sql 语句，该语句使用了我在存储过程顶部声明的两个临时表，但是由于该动态 sql 语句创建了一个新范围，我无法使用这些临时表。
Solution:
解决方案：
I simply changed them to Global Temporary Variables and they worked.
我只是将它们更改为全局临时变量并且它们起作用了。
Find my stored procedure underneath.
在下面找到我的存储过程。
CREATE PROCEDURE RAFCustom_Room_GetRelatedProducts
-- Add the parameters for the stored procedure here
@PRODUCT_SKU nvarchar(15) = Null
AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
AS BEGIN -- 添加了 SET NOCOUNT ON 以防止额外的结果集 -- 干扰 SELECT 语句。设置无计数；
IF OBJECT_ID('tempdb..##RelPro', 'U') IS NOT NULL
BEGIN
DROP TABLE ##RelPro
END
Create Table ##RelPro
(
RowID int identity(1,1),
ID int,
Item_Name nvarchar(max),
SKU nvarchar(max),
Vendor nvarchar(max),
Product_Img_180 nvarchar(max),
rpGroup int,
Assoc_Item_1 nvarchar(max),
Assoc_Item_2 nvarchar(max),
Assoc_Item_3 nvarchar(max),
Assoc_Item_4 nvarchar(max),
Assoc_Item_5 nvarchar(max),
Assoc_Item_6 nvarchar(max),
Assoc_Item_7 nvarchar(max),
Assoc_Item_8 nvarchar(max),
Assoc_Item_9 nvarchar(max),
Assoc_Item_10 nvarchar(max)
);
Begin
Insert ##RelPro(ID, Item_Name, SKU, Vendor, Product_Img_180, rpGroup)
Select distinct zp.ProductID, zp.Name, zp.SKU,
(Select m.Name From ZNodeManufacturer m(nolock) Where m.ManufacturerID = zp.ManufacturerID),
'http://s0001.server.com/is/sw11/DG/' +
(Select m.Custom1 From ZNodeManufacturer m(nolock) Where m.ManufacturerID = zp.ManufacturerID) +
'_' + zp.SKU + '_3?$SC_3243$', ep.RoomID
From Product zp(nolock) Inner Join RF_ExtendedProduct ep(nolock) On ep.ProductID = zp.ProductID
Where zp.ActiveInd = 1 And SUBSTRING(zp.SKU, 1, 2) <> 'GC' AND zp.Name <> 'PLATINUM' AND zp.SKU = (Case When @PRODUCT_SKU Is Not Null Then @PRODUCT_SKU Else zp.SKU End)
End
declare @curr_row int = 0,
@tot_rows int= 0,
@sku nvarchar(15) = null;
IF OBJECT_ID('tempdb..##TSku', 'U') IS NOT NULL
BEGIN
DROP TABLE ##TSku
END
Create Table ##TSku (tid int identity(1,1), relsku nvarchar(15));
Select @curr_row = (Select MIN(RowId) From ##RelPro);
Select @tot_rows = (Select MAX(RowId) From ##RelPro);
while @curr_row <= @tot_rows
Begin
select @sku = SKU from ##RelPro where RowID = @curr_row;
truncate table ##TSku;
Insert ##TSku(relsku)
Select distinct top(10) tzp.SKU From Product tzp(nolock) INNER JOIN
[INTRANET].raf_FocusAssociatedItem assoc(nolock) ON assoc.associatedItemID = tzp.SKU
Where (assoc.isActive=1) And (tzp.ActiveInd = 1) AND (assoc.productID = @sku)
declare @curr_row1 int = (Select Min(tid) From ##TSku),
@tot_rows1 int = (Select Max(tid) From ##TSku);
If(@tot_rows1 <> 0)
Begin
While @curr_row1 <= @tot_rows1
Begin
declare @col_name nvarchar(15) = null,
@sqlstat nvarchar(500) = null;
set @col_name = 'Assoc_Item_' + Convert(nvarchar(2), @curr_row1);
set @sqlstat = 'update ##RelPro set ' + @col_name + ' = (Select relsku From ##TSku Where tid = ' + Convert(nvarchar(2), @curr_row1) + ') Where RowID = ' + Convert(nvarchar(2), @curr_row);
Exec(@sqlstat);
set @curr_row1 = @curr_row1 + 1;
End
End
set @curr_row = @curr_row + 1;
End
Select * From ##RelPro;
END GO
结束
回答by Gerald
Here is an example of using a dynamic T-SQL query and then extracting the results should you have more than one column of returned values (notice the dynamic table name):
下面是一个使用动态 T-SQL 查询的示例，如果您有不止一列返回值（注意动态表名），则提取结果：
DECLARE
@strSQLMain nvarchar(1000),
@recAPD_number_key char(10),
@Census_sub_code varchar(1),
@recAPD_field_name char(100),
@recAPD_table_name char(100),
@NUMBER_KEY varchar(10),
if object_id('[Permits].[dbo].[myTempAPD_Txt]') is not null
DROP TABLE [Permits].[dbo].[myTempAPD_Txt]
CREATE TABLE [Permits].[dbo].[myTempAPD_Txt]
(
[MyCol1] char(10) NULL,
[MyCol2] char(1) NULL,
)
-- an example of what @strSQLMain is : @strSQLMain = SELECT @recAPD_number_key = [NUMBER_KEY], @Census_sub_code=TEXT_029 FROM APD_TXT0 WHERE Number_Key = '01-7212'
SET @strSQLMain = ('INSERT INTO myTempAPD_Txt SELECT [NUMBER_KEY], '+ rtrim(@recAPD_field_name) +' FROM '+ rtrim(@recAPD_table_name) + ' WHERE Number_Key = '''+ rtrim(@Number_Key) +'''')
EXEC (@strSQLMain)
SELECT @recAPD_number_key = MyCol1, @Census_sub_code = MyCol2 from [Permits].[dbo].[myTempAPD_Txt]
DROP TABLE [Permits].[dbo].[myTempAPD_Txt]
回答by sfomate
Using Temp table solves the problem but I ran into issues using Exec so I went with the following solution of using sp_executesql:
使用 Temp 表解决了问题，但我在使用 Exec 时遇到了问题，所以我使用了以下使用 sp_executesql 的解决方案：
Create TABLE #tempJoin ( Old_ID int, New_ID int);
declare @table_name varchar(128);
declare @strSQL nvarchar(3072);
set @table_name = 'Object';
--build sql sting to execute
set @strSQL='INSERT INTO '+@table_name+' SELECT '+@columns+' FROM #tempJoin CJ
Inner Join '+@table_name+' sourceTbl On CJ.Old_ID = sourceTbl.Object_ID'
**exec sp_executesql @strSQL;**
