동적 SQL 문에서 테이블 변수를 사용하는 방법은 무엇입니까?

동적 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"를 선언해야 합니다.

동적 쿼리의 문자열 블록 밖으로 테이블을 가져가려고 했지만 소용이 없었습니다.

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열은 하위 범위에서 참조되는 테이블 변수가 복사본이 아닌 외부 범위와 확실히 동일하다는 것을 보여주기 위해 포함됩니다.

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을 사용할 필요가 없습니다.

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

글쎄요, 저는 방법을 생각해냈고 같은 문제에 부딪힐 수 있는 사람들과 공유하려고 생각했습니다.

제가 직면했던 문제부터 시작하겠습니다.

저장 프로시저 맨 위에 선언한 두 개의 임시 테이블을 사용하는 Dynamic Sql 문을 실행하려고 했지만, 동적 SQL 문이 새 범위를 생성했기 때문에 임시 테이블을 사용할 수 없었습니다.

솔루션:

저는 그것들을 글로벌 임시 변수로 변경했고 그들은 작동했습니다.

아래에 있는 저장 프로시저를 찾습니다.

CREATE PROCEDURE RAFCustom_Room_GetRelatedProducts
-- Add the parameters for the stored procedure here
@PRODUCT_SKU nvarchar(15) = Null

AS BEGIN -- 추가 결과 집합을 방지하기 위해 SET NO COUNT ON added -- 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;

종료 시작

테이블 변수는 선언한 범위 내에서만 존재하는 것으로 알고 있기 때문에 (아래 업데이트 참조) 불가능하다고 생각합니다.그러나 임시 테이블을 사용할 수 있습니다.create table구문 및 테이블 이름 앞에 # 기호를 붙입니다. 그러면 테이블 이름을 만드는 범위와 동적 문의 범위 내에서 모두 액세스할 수 있습니다.

업데이트: 테이블 값 매개 변수를 사용하여 동적 SQL 문으로 테이블 변수를 전달하는 방법은 Martin Smith의 답변을 참조하십시오.또한 언급된 제한 사항에 유의하십시오. 테이블 값 매개 변수는 읽기 전용입니다.

다음은 동적 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]  

Temp table을 사용하면 문제가 해결되지만 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;**

언급URL : https://stackoverflow.com/questions/4626292/how-to-use-table-variable-in-a-dynamic-sql-statement