Как использовать табличную переменную в динамической инструкции sql?

в 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 

The 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 создало новую область, я не мог использовать временные таблицы.

устранение:

Я просто изменил их к глобальным временным переменным и они работали.

найдите мою хранимую процедуру внизу.

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

как НАЧИНАТЬ -- Установите NOCOUNT на добавлено, чтобы предотвратить дополнительные результирующие наборы от -- вмешательство в инструкции SELECT. УСТАНОВИТЕ 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;

конец Иди

Я не думаю, что это возможно (хотя см. ниже); насколько я знаю a переменной в таблице существует только в пределах объявленной области. Однако, вы можете использовать временная таблица (используйте create table синтаксис и префикс имени таблицы с символом#), и это будет доступно как в области, которая его создает, так и в области вашего динамического оператора.

UPDATE: см. ответ Мартина Смита о том, как использовать a возвращающий табличное значение параметр для передачи табличной переменной в динамическую инструкцию SQL. Также обратите внимание на упомянутое ограничение: возвращающие табличное значение параметры доступны только для чтения.

использование временной таблицы решает проблему, но я столкнулся с проблемами с помощью 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;**

вот пример использования динамического запроса 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]