How to use table variable in a dynamic sql statement?

In my stored procedure I declared two table variables on top of my procedure. Now I am trying to use that table variable within a dynamic sql statement but I get this error at the time of execution of that procedure. I am using Sql Server 2008.

This is how my query looks like,

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

And I get the following errors,

Must declare the table variable "@RelPro". Must declare the table variable "@TSku".

I have tried to take the table outside of the string block of dynamic query but to no avail.

On SQL Server 2008+ it is possible to use Table Valued Parameters to pass in a table variable to a dynamic SQL statement as long as you don't need to update the values in the table itself.

So from the code you posted you could use this approach for @TSku but not for @RelPro

Example syntax below.

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 column is included just to demonstrate that the table variable referenced in the child scope is definitely the same one as the outer scope rather than a copy.

Your EXEC executes in a different context, therefore it is not aware of any variables that have been declared in your original context. You should be able to use a temp table instead of a table variable as shown in the simple demo below.

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

You don't have to use dynamic 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;

You can't do this because the table variables are out of scope.

You would have to declare the table variable inside the dynamic SQL statement or create temporary tables.

I would suggest you read this excellent article on dynamic SQL.

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

I don't think that is possible (though refer to the update below); as far as I know a table variable only exists within the scope that declared it. You can, however, use a temp table (use the create table syntax and prefix your table name with the # symbol), and that will be accessible within both the scope that creates it and the scope of your dynamic statement.

UPDATE: Refer to Martin Smith's answer for how to use a table-valued parameter to pass a table variable in to a dynamic SQL statement. Also note the limitation mentioned: table-valued parameters are read-only.

Well, I figured out the way and thought to share with the people out there who might run into the same problem.

Let me start with the problem I had been facing,

I had been trying to execute a Dynamic Sql Statement that used two temporary tables I declared at the top of my stored procedure, but because that dynamic sql statment created a new scope, I couldn't use the temporary tables.

Solution:

I simply changed them to Global Temporary Variables and they worked.

Find my stored procedure underneath.

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

AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. 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

Here is an example of using a dynamic T-SQL query and then extracting the results should you have more than one column of returned values (notice the dynamic table name):

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]	

Using Temp table solves the problem but I ran into issues using Exec so I went with the following solution of using 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;**