[SQL] sp_executesql을 표와 변수를 사용하여

sp_executesql을 표와 변수를 사용하여

나는 테이블 변수를 포함하는 쿼리를 가지고 :

DECLARE @Selects    XML ;
SET @Selects='<Selects><Select><Q_ID>1</Q_ID><Q_DESC>nima1</Q_DESC></Select><Select><Q_ID>2</Q_ID><Q_DESC>nima2</Q_DESC></Select><Select><Q_ID>3</Q_ID><Q_DESC>nima3</Q_DESC></Select></Selects>'

DECLARE @QuestionID     NVARCHAR(10);
SET @QuestionID='a5';

DECLARE @TblSelect  TABLE 
(
    Q_ID            INT,
    Q_DESC          NVARCHAR(500)
)

INSERT INTO @TblSelect
(
    Q_ID,Q_DESC
)
SELECT  Q_Select.value('(Q_ID)[1]', 'int') AS 'Q_ID',
        Q_Select.value('(Q_DESC)[1]', 'nvarchar(500)') AS 'Q_DESC'
FROM    @Selects.nodes('/Selects/Select') AS AllSelects(Q_Select) 

DECLARE @Query      NVARCHAR(4000);
SET @Query=N'SELECT Q_ID,COUNT(Q_ID) FROM @TblSelect LEFT OUTER JOIN tblbase tb ON @TblSelect.Q_ID = @Col_Select group by Q_ID';

EXECUTE sp_executesql @Query,@TblSelect,@Col_Select

어떻게 쿼리에 테이블 변수를 전달할 수 있습니다?

해결법

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

   1.여기에서 sp_executesql하는 테이블 반환 매개 변수를 전달하는 방법의 예입니다. 변수는 읽기 전용 전달 될 수있다 :

   여기에서 sp_executesql하는 테이블 반환 매개 변수를 전달하는 방법의 예입니다. 변수는 읽기 전용 전달 될 수있다 :

   if exists (select * from sys.types where name = 'TestTableType')
       drop type TestTableType
   
   create type TestTableType as table (id int)
   go
   declare @t TestTableType
   insert @t select 6*7
   
   exec sp_executesql N'select * from @var', N'@var TestTableType readonly', @t
   

   이것은 생명의 궁극적 인 질문, 우주, 그리고 모든 답을 인쇄합니다.

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

   2.테이블 타입을보십시오

   테이블 타입을보십시오

   DECLARE @Selects    XML ;
       SET @Selects='<Selects><Select><Q_ID>1</Q_ID><Q_DESC>nima1</Q_DESC></Select><Select><Q_ID>2</Q_ID><Q_DESC>nima2</Q_DESC></Select><Select><Q_ID>3</Q_ID><Q_DESC>nima3</Q_DESC></Select></Selects>'
   
       DECLARE @QuestionID     NVARCHAR(10);
       SET @QuestionID='a5';
   
       DECLARE TYPE TblSelect  AS TABLE 
       (
           Q_ID            INT,
           Q_DESC          NVARCHAR(500)
       )
   
       /* Declare a variable that references the type. */
       DECLARE @TblSelect 
       AS TblSelect ;
   
       INSERT INTO @TblSelect 
       (
           Q_ID,Q_DESC
       )
   
       SELECT  Q_Select.value('(Q_ID)[1]', 'int') AS 'Q_ID',
               Q_Select.value('(Q_DESC)[1]', 'nvarchar(500)') AS 'Q_DESC'
       FROM    @Selects.nodes('/Selects/Select') AS AllSelects(Q_Select) 
   
       DECLARE @Query      NVARCHAR(4000);
       SET @Query=N'SELECT Q_ID,COUNT(Q_ID) FROM @TblSelect LEFT OUTER JOIN tblbase tb ON @TblSelect.Q_ID = @Col_Select group by Q_ID';
   
       EXECUTE sp_executesql @Query,@TblSelect,@Col_Select
   

from https://stackoverflow.com/questions/7329996/using-table-variable-with-sp-executesql by cc-by-sa and MIT license