Como usar a variável de tabela em uma instrução sql dinâmica?

No meu procedimento armazenado, declarei duas variables ​​de tabela no topo do meu procedimento. Agora estou tentando usar essa variável de tabela dentro de uma instrução sql dinâmica, mas recebo esse erro no momento da execução desse procedimento. Eu estou usando o Sql Server 2008.

É assim que minha consulta parece

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

E eu recebo os seguintes erros,

Deve declarar a variável de tabela “@RelPro”. Deve declarar a variável de tabela “@TSku”.

Eu tentei tirar a tabela fora do bloco de string da consulta dinâmica, mas sem sucesso.

    Seu EXEC é executado em um contexto diferente, portanto, não está ciente de nenhuma variável que tenha sido declarada em seu contexto original. Você deve poder usar uma tabela temporária em vez de uma variável de tabela, como mostrado na demonstração simples abaixo.

     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 

    No SQL Server 2008+, é possível usar parameters de Tabela de Valores para passar uma variável de tabela para uma instrução SQL dinâmica, desde que você não precise atualizar os valores na própria tabela.

    Então, a partir do código que você postou, você poderia usar essa abordagem para @TSku mas não para @RelPro

    Exemplo de syntax abaixo.

     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 

    A coluna physloc é incluída apenas para demonstrar que a variável de tabela referenciada no escopo filho é definitivamente a mesma que o escopo externo, em vez de uma cópia.

    Você não precisa usar SQL dynamic

     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; 

    Você não pode fazer isso porque as variables ​​da tabela estão fora do escopo.

    Você teria que declarar a variável table dentro da instrução SQL dinâmica ou criar tabelas temporárias.

    Eu sugiro que você leia este excelente artigo sobre SQL dynamic.

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

    Bem, eu descobri o caminho e pensei em compartilhar com as pessoas que poderiam ter o mesmo problema.

    Deixe-me começar com o problema que eu estava enfrentando,

    Eu estava tentando executar uma instrução Dynamic Sql que usou duas tabelas temporárias que declarei no início do meu procedimento armazenado, mas como essa instrução sql dinâmica criou um novo escopo, não consegui usar as tabelas temporárias.

    Solução:

    Eu simplesmente mudei para Global Temporary Variables e eles funcionaram.

    Encontre o meu procedimento armazenado por baixo.

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

    AS BEGIN – SET NOCOUNT ON adicionado para impedir que conjuntos de resultados extra interfiram nas instruções 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; 

    END GO

    Eu não acho que isso é possível ( embora refira-se à atualização abaixo ); Tanto quanto eu sei, uma variável de tabela só existe dentro do escopo que a declarou. Você pode, no entanto, usar uma tabela temporária (use a syntax create table e prefixar o nome da tabela com o símbolo #), e isso será acessível dentro do escopo que a cria e do escopo de sua instrução dinâmica.

    ATUALIZAÇÃO: Consulte a resposta de Martin Smith sobre como usar um parâmetro com valor de tabela para transmitir uma variável de tabela para uma instrução SQL dinâmica. Observe também a limitação mencionada: os parâmetros com valor de tabela são somente leitura.

    Usando a tabela Temp resolve o problema, mas eu corri para problemas usando o Exec então fui com a seguinte solução de usar 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;** 

    Aqui está um exemplo de como usar uma consulta T-SQL dinâmica e, em seguida, extrair os resultados caso você tenha mais de uma coluna de valores retornados (observe o nome da tabela dinâmica):

     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]