Alex Rivera | Logout

How to use table variable in a dynamic sql statement?

Asked 2011-01-07T14:12:49.403
78

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.

Edit
Report

1 Answer

2

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.

answered 2011-01-07T14:18:51.280

Your Answer