stored - tablas temporales sql server 2016

¿Cómo usar la variable de tabla en una declaración dinámica de sql?

Question

En mi procedimiento almacenado, declare dos variables de tabla sobre mi procedimiento. Ahora estoy tratando de usar esa variable de tabla dentro de una declaración SQL dinámica, pero obtengo este error al momento de la ejecución de ese procedimiento. Estoy usando Sql Server 2008.

Así es como se ve mi consulta,

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

Y obtengo los siguientes errores

Debe declarar la variable de tabla "@RelPro". Debe declarar la variable de tabla "@TSku".

Intenté sacar la tabla fuera del bloque de cadena de la consulta dinámica pero fue en vano.

Answer 1

Aquí hay un ejemplo del uso de una consulta dinámica de T-SQL y luego extraer los resultados en caso de que tenga más de una columna de valores devueltos (observe el nombre de la tabla 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]

Answer 2

Bueno, descubrí el camino y pensé en compartir con la gente que podría encontrarse con el mismo problema.

Permítanme comenzar con el problema al que me estaba enfrentando,

Estuve tratando de ejecutar un Dynamic Sql Statement que usaba dos tablas temporales que declaraba en la parte superior de mi procedimiento almacenado, pero como esa declaración dinámica de sql creó un nuevo ámbito, no pude usar las tablas temporales.

Solución:

Simplemente los cambié a Variables temporales globales y funcionaron.

Encuentra mi procedimiento almacenado debajo.

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

COMO COMENZAR - SET NOCOUNT ON added para evitar conjuntos de resultados extra - interfiere con las instrucciones 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

Answer 3

El uso de la tabla Temp resuelve el problema, pero me encontré con problemas utilizando Exec, así que fui con la siguiente solución 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;**

Answer 4

En SQL Server 2008+, es posible usar parámetros de valores de tabla para pasar una variable de tabla a una declaración de SQL dinámico, siempre que no necesite actualizar los valores en la tabla en sí.

Entonces, desde el código que publicaste, podrías usar este enfoque para @TSku pero no para @RelPro

Ejemplo de sintaxis a continuación.

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

La columna physloc se incluye solo para demostrar que la variable de tabla a la que se hace referencia en el ámbito hijo es definitivamente la misma que el ámbito externo en lugar de una copia.

Answer 5

No tiene que usar SQL dinámico

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;

Answer 6

No creo que eso sea posible ( aunque consulte la actualización a continuación ); Hasta donde yo sé, una variable de tabla solo existe dentro del alcance que la declaró. Sin embargo, puede usar una tabla temporal (use la sintaxis de la create table y prefija el nombre de su tabla con el símbolo #), y estará accesible tanto dentro del alcance que la crea como en el alcance de su declaración dinámica.

ACTUALIZACIÓN: Consulte la respuesta de Martin Smith sobre cómo usar un parámetro con valores de tabla para pasar una variable de tabla a una instrucción SQL dinámica. También tenga en cuenta la limitación mencionada: los parámetros con valores de tabla son de solo lectura.

Answer 7

No puede hacer esto porque las variables de la tabla están fuera del alcance.

Debería declarar la variable de la tabla dentro de la declaración de SQL dinámico o crear tablas temporales.

Sugeriría que leyeras este excelente artículo sobre SQL dinámico.

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

Answer 8

Su EXEC se ejecuta en un contexto diferente, por lo tanto, no tiene conocimiento de ninguna variable que haya sido declarada en su contexto original. Debería poder usar una tabla temporal en lugar de una tabla como se muestra en la demostración simple a continuación.

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