[SQL] 어떻게 동적 SQL 문에서 테이블 변수를 사용하는 방법?

어떻게 동적 SQL 문에서 테이블 변수를 사용하는 방법?

내 저장 프로 시저에서 나는 내 절차의 상단에 두 개의 테이블 변수를 선언했다. 지금은 동적 SQL 문 내에서 해당 테이블 변수를 사용하려고하지만 난 그 절차의 실행시이 오류가 발생합니다. 내가는 SQL 서버 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"를 선언해야합니다.

나는 동적 쿼리의하지만 아무 소용이 문자열 블록의 테이블 바깥을 위해 노력했다.

해결법

1. ==============================

   1.귀하의 EXEC 그러므로 그것은 당신의 원본 문장에 선언 된 모든 변수를 인식하지, 다른 컨텍스트에서 실행됩니다. 아래의 간단한 데모에서와 같이 당신은 테이블 변수 대신 임시 테이블을 사용할 수 있어야합니다.

   귀하의 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
   

2. ==============================

   2.SQL 서버에서 2008+ 당신이 테이블 자체의 값을 업데이트 할 필요가 없습니다만큼 동적 SQL 문에 테이블 변수에 전달하는 매개 변수 소중한 표를 사용할 수 있습니다.

   SQL 서버에서 2008+ 당신이 테이블 자체의 값을 업데이트 할 필요가 없습니다만큼 동적 SQL 문에 테이블 변수에 전달하는 매개 변수 소중한 표를 사용할 수 있습니다.

   그래서 코드에서 당신은 당신이 @RelPro에 대한 @TSku을 위해이 방법을 사용하지만 수 없었다 게시

   아래 구문 예는.

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

3. ==============================

   3.당신은 동적 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;
   

4. ==============================

   4.테이블 변수가 범위를 벗어나 있기 때문에이 작업을 수행 할 수 없습니다.

   테이블 변수가 범위를 벗어나 있기 때문에이 작업을 수행 할 수 없습니다.

   당신은 동적 SQL 문 내에서 테이블 변수를 선언하거나 임시 테이블을 생성해야합니다.

   나는 당신이 동적 SQL에이 우수한 기사를 읽고 건의 할 것입니다.

   http://www.sommarskog.se/dynamic_sql.html

5. ==============================

   5.글쎄, 난 길을 파악하고 같은 문제로 실행 할 수있는 사람이 밖으로 사람들과 공유로 생각했다.

   글쎄, 난 길을 파악하고 같은 문제로 실행 할 수있는 사람이 밖으로 사람들과 공유로 생각했다.

   나를 내가 직면했던 문제부터 시작하자

   나는 내 저장 프로 시저의 상단에 선언 된 두 개의 임시 테이블을 사용하는 동적 SQL 문을 실행하기 위해 노력했지만, 그 동적 SQL 한 Statment 새로운 영역을 생성하기 때문에, 나는 임시 테이블을 사용할 수 없었다.

   해결책:

   단순히 글로벌 임시 변수로 변경하고는했다.

   내 저장 프로 시저 아래를 찾을 수 있습니다.

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

   같이 BEGIN     - SET NOCOUNT ON은에서 추가 결과 집합을 방지하기 위해 추가     - SELECT 문을 방해.     SET NOCOUNT ON;

   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;
   

   종료 가다

6. ==============================

   6.나는 그 (하지만 아래의 업데이트 참조) 가능하다고 생각하지 않는다; 내가 아는 한 테이블 변수는 그것을 선언 된 범위 내에서 존재한다. 당신은, 그러나, ((가) # 기호로 테이블 구문과 접두사 테이블 이름을 만들 사용) 임시 테이블을 사용할 수 있습니다, 그게 및 동적 문의 범위를 생성 범위 모두에서 액세스 할 수 있습니다.

   나는 그 (하지만 아래의 업데이트 참조) 가능하다고 생각하지 않는다; 내가 아는 한 테이블 변수는 그것을 선언 된 범위 내에서 존재한다. 당신은, 그러나, ((가) # 기호로 테이블 구문과 접두사 테이블 이름을 만들 사용) 임시 테이블을 사용할 수 있습니다, 그게 및 동적 문의 범위를 생성 범위 모두에서 액세스 할 수 있습니다.

   UPDATE : 동적 SQL 문에서 테이블 변수를 전달하는 테이블 반환 매개 변수를 사용하는 방법에 대한 마틴 스미스의 대답을 참조하십시오. 또한 제한이 언급주의 : 테이블 반환 매개 변수는 읽기 전용입니다.

7. ==============================

   7.여기서 동적 T-SQL 쿼리를 사용하여 다음 리턴 값 (통지 동적 테이블 이름)의 하나 이상의 열을 가져야 결과를 추출하는 예이다 :

   여기서 동적 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]  
   

8. ==============================

   8.임시 테이블을 사용하여 문제를 해결하지만 난을 sp_executesql을 사용하여 다음과 같은 솔루션을 갔다 있도록 Exec이 사용 문제로 실행 :

   임시 테이블을 사용하여 문제를 해결하지만 난을 sp_executesql을 사용하여 다음과 같은 솔루션을 갔다 있도록 Exec이 사용 문제로 실행 :

   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;**
   

from https://stackoverflow.com/questions/4626292/how-to-use-table-variable-in-a-dynamic-sql-statement by cc-by-sa and MIT license