How To Execute A Sql String That References A Table Variable?

I have a table variable in SQL Server 2008 DECLARE @specsAndModel TABLE ( specName VARCHAR(50) ,specVal VARCHAR(50) ) INSERT INTO @specsAndModel

Solution 1:

The table you are creating is a table variable which is not available outside of its initial scope. There are a few ways to fix this:

Create a Global Temp Table (Disclaimer: this can cause problems if more that one user attempts to run this at the same time.):

createtable  ##specsAndModel 
(
    specName VARCHAR(50)
    ,specVal VARCHAR(50)
)
INSERTINTO ##specsAndModel
VALUES('[modelNumber]', 'F00-B4R')

DECLARE@query NVARCHAR(MAX);
SET@query='SELECT specName, specVal FROM ##specsAndModel'EXECUTE(@query)

Create a Local Temp Table instead of global:

createtable  #specsAndModel 
(
    specName VARCHAR(50)
    ,specVal VARCHAR(50)
)
INSERTINTO #specsAndModel
VALUES('[modelNumber]', 'F00-B4R')

DECLARE@query NVARCHAR(MAX);
SET@query='SELECT specName, specVal FROM #specsAndModel'EXECUTE(@query)

Execute the create table inside of your dynamic SQL (ugly):

DECLARE@query NVARCHAR(MAX);
SET@query='DECLARE @specsAndModel TABLE
(
    specName VARCHAR(50)
    ,specVal VARCHAR(50)
)
INSERT INTO @specsAndModel
VALUES(''[modelNumber]'', ''F00-B4R'')
SELECT specName, specVal FROM @specsAndModel'exec(@query)

Instead of using a temp table, create an actual table and then drop it when done (Disclaimer: this can cause problems if more that one user attempts to run this at the same time.):

createTABLE specsAndModel 
(
    specName VARCHAR(50)
    ,specVal VARCHAR(50)
)
INSERTINTO specsAndModel
VALUES('[modelNumber]', 'F00-B4R')

DECLARE@query NVARCHAR(MAX);
SET@query='SELECT specName, specVal FROM specsAndModel'EXECUTE(@query)  

droptable specsAndModel

Here is an link to a discussion about temp tables and table variables:

Should I use a #temp table or a @table variable?

Edit: You can pass in a table variable using sp_executesql:

create type specsAndModel astable (
    specName VARCHAR(50)
    ,specVal VARCHAR(50)
 )
go
declare@t specsAndModel
insert@tVALUES('[modelNumber]', 'F00-B4R')

exec sp_executesql N'select specName, specVal from @var', N'@var specsAndModel readonly', @t

Using either the global ##temp tables and a permanent table run risks in that if more than one users attempts to run the process, there could be conflicts.

You are safer using either a local #temp table or passing the table variable using sp_executesql.

Solution 2:

You also have to create your table variable inside the in the string.

DECLARE@query NVARCHAR(MAX);
SET@query='DECLARE @specsAndModel TABLE  ( specName VARCHAR(50) ,specVal VARCHAR(50))'SET@Query=@Query+' INSERT INTO  @specsAndModel VALUES(''modelNumber'',''abcd''); SELECT specName, specVal FROM @specsAndModel'EXEC (@query)