동적 SQL 문에서 테이블 변수를 사용하는 방법은 무엇입니까?
내 저장 프로 시저에서 내 프로 시저 위에 두 개의 테이블 변수를 선언했습니다. 이제 동적 SQL 문 내에서 해당 테이블 변수를 사용하려고하지만 해당 프로 시저 실행시이 오류가 발생합니다. Sql Server 2008을 사용하고 있습니다.
이것이 내 쿼리의 모습입니다.
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);
그리고 다음과 같은 오류가 발생합니다.
테이블 변수 "@RelPro"를 선언해야합니다. 테이블 변수 "@TSku"를 선언해야합니다.
동적 쿼리의 문자열 블록 외부에서 테이블을 가져 오려고 시도했지만 아무 소용이 없습니다.
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
SQL Server 2008+에서는 테이블 자체의 값을 업데이트 할 필요가없는 한 테이블 값 매개 변수를 사용하여 테이블 변수를 동적 SQL 문에 전달할 수 있습니다.
따라서 게시 한 코드 에서이 접근 방식을 사용할 수는 @TSku있지만@RelPro
아래 구문 예.
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
physloc열은 바로 아이의 범위에서 참조하는 테이블 변수는 확실히 외부 범위가 아닌 사본과 동일 하나라는 것을 보여주기 위해 포함되어 있습니다.
당신은하지 않습니다 이 동적 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;
테이블 변수가 범위를 벗어 났기 때문에이 작업을 수행 할 수 없습니다.
동적 SQL 문 내에서 테이블 변수를 선언하거나 임시 테이블을 만들어야합니다.
동적 SQL에 대한이 훌륭한 기사를 읽어 보시기 바랍니다.
http://www.sommarskog.se/dynamic_sql.html
글쎄, 나는 방법을 알아 냈고 같은 문제에 부딪 힐지도 모르는 사람들과 공유하려고 생각했습니다.
제가 직면했던 문제부터 시작하겠습니다.
내 저장 프로 시저의 맨 위에 선언 한 두 개의 임시 테이블을 사용하는 동적 SQL 문을 실행하려고했지만 해당 동적 SQL 문이 새 범위를 생성했기 때문에 임시 테이블을 사용할 수 없었습니다.
해결책:
간단히 Global Temporary Variables로 변경하고 작동했습니다.
아래에서 내 저장 프로 시저를 찾습니다.
CREATE PROCEDURE RAFCustom_Room_GetRelatedProducts
-- Add the parameters for the stored procedure here
@PRODUCT_SKU nvarchar(15) = Null
AS BEGIN-SELECT 문을 방해하는 추가 결과 집합을 방지하기 위해 SET NOCOUNT ON이 추가되었습니다. NOCOUNT 설정;
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;
끝 이동
I don't think that is possible (though refer to the update below); as far as I know a table variable only exists within the scope that declared it. You can, however, use a temp table (use the create table syntax 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.
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.
Using Temp table solves the problem but I ran into issues using Exec so I went with the following solution of using 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;**
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):
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]
참고URL : https://stackoverflow.com/questions/4626292/how-to-use-table-variable-in-a-dynamic-sql-statement
'code' 카테고리의 다른 글
| NSMutableArray 또는 다른 컨테이너에 NSRange를 어떻게 저장합니까? (0) | 2020.12.10 |
|---|---|
| Android SDK 설치 : JDK (Java SE Development Kit)를 찾을 수 없음 (0) | 2020.12.10 |
| Python 파일에 저장 (0) | 2020.12.10 |
| 웹 스톰에서 자동 저장을 끄는 방법 (0) | 2020.12.10 |
| IE에서 transform : translateX와 함께 CSS calc ()를 사용할 수 없습니다. (0) | 2020.12.10 |