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

Я пытался вывести таблицу за пределы строкового блока динамического запроса, но безрезультатно.

Ответы на вопрос(8)

потому что переменные таблицы находятся вне области видимости.

Вы должны будете объявить переменную таблицы внутри оператора динамического SQL или создать временные таблицы.

Я бы посоветовал вам прочитать эту прекрасную статью о динамическом SQL.

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

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

ОБНОВЛЕНИЕ: Обратитесь к ответу Мартина Смита о том, как использовать табличный параметр для передачи табличной переменной в динамический оператор 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]  
Решение Вопроса

поэтому он не знает ни о каких переменных, которые были объявлены в вашем исходном контексте. Вы должны иметь возможность использовать временную таблицу вместо табличной переменной, как показано в простой демонстрации ниже.

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
 FrenkyB20 мар. 2017 г., 06:25
Возможно, какая-то хорошая ссылка на контекст сервера sql? Я действительно не понимаю этого. Если динамический sql и табличная переменная находятся внутри одного пакета - какую роль здесь играет контекст?
 Aaron Bertrand23 апр. 2015 г., 23:16
@John Нет, внутренняя область видна #table, созданной в родительской области видимости. Легко проверить.
 John Spiegel24 мар. 2015 г., 16:12
Возможно, я запутался, но разве другой контекст также не приводит к тому, что временная таблица выходит из области видимости, если только не используется глобальная временная таблица?

я нашел способ и подумал поделиться с людьми, которые могут столкнуться с той же проблемой.

Позвольте мне начать с проблемы, с которой я столкнулся,

Я пытался выполнить динамический оператор Sql, который использовал две временные таблицы, которые я объявил в верхней части своей хранимой процедуры, но поскольку эта динамическая SQL-статистика создала новую область, я не мог использовать временные таблицы.

Решение:

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

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

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

AS BEGIN - добавлено SET NOCOUNT ON, чтобы дополнительные наборы результатов не мешали инструкциям SELECT. УСТАНАВЛИВАЙТЕ 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

END GO

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

END GO

 Shadow Wizard26 мар. 2012 г., 12:50
Пожалуйста, отметьте этот ответ как принятый.
 Dr. Wily's Apprentice07 янв. 2011 г., 15:49
Я не проверял ваш код, но я не думаю, что ваши временные таблицы должны бытьГлобальный, Вы, вероятно, можете использовать неглобальную (локальную?) Временную таблицу (просто используйте один символ #). Преимущество заключается в том, что оно автоматически исчезнет, ​​как только ваше соединение будет закрыто, и оно будет видно только внутри вашего соединения. То есть Я считаю, что с глобальной временной таблицей, если у вас есть несколько соединений, выполняющих одну и ту же процедуру в одно и то же время, они могут сожрать друг друга из-за использования одной и той же глобальной временной таблицы.
 Martin Smith07 янв. 2011 г., 16:20
@Dr. Вилли - Да. Я думаю, что глобальные временные таблицы необходимы, если они создаются внутри самого динамического SQL, чтобы не допустить их исчезновения по окончании пакета динамического SQL, но если они созданы вне пакета динамического SQL, локальные временные таблицы должны быть видны.
 Dr. Wily's Apprentice07 янв. 2011 г., 16:29
@Martin - Ах, я только что попробовал это, и похоже, что вы правы; временная таблица, созданная внутри динамического оператора, исчезает. Тем не менее, это не похоже на сценарий здесь. Временные таблицы создаются и затем изменяются динамическими операторами.
 gbn07 янв. 2011 г., 16:18
Извините, у вас тоже есть курсор ...?

но я столкнулся с проблемами при использовании 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 '[email protected]_name+' SELECT '[email protected]+' FROM #tempJoin CJ
                        Inner Join '[email protected]_name+' sourceTbl On CJ.Old_ID = sourceTbl.Object_ID'

**exec sp_executesql @strSQL;**

Ты неимеют использовать динамический 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;
 JNK07 янв. 2011 г., 17:30
+1 для не динамического решения SQL

табличной переменной в динамический оператор 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',
  @[email protected] 

physloc столбец включен только для демонстрации того, что переменная таблицы, на которую ссылается дочерняя область, определенно такая же, как внешняя область, а не копия.

 James Poulose21 авг. 2013 г., 07:37
Я не понимаю, почему за это решение не проголосовали достаточно! Отличное и элегантное решение.
 SheldonH30 апр. 2015 г., 00:18
Я понял, что он передает таблицу по ссылке, поэтому сокращается количество операций ввода-вывода. У Havent не было возможности проверить это, хотя в случае вложенного хранимого вызова proc или оператора execute

Ваш ответ на вопрос