How to execute a SQL String that references a table variable?

Question

I have a table variable in SQL Server 2008

    DECLARE @specsAndModel TABLE
    (
        specName VARCHAR(50)
        ,specVal VARCHAR(50)
    )
    INSERT INTO @specsAndModel
    VALUES('[modelNumber]', 'F00-B4R')

Then, I later build a string called @query, which I ultimately try to pass into EXECUTE, as in the following example:

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

However, SQL Server gives me the error message: Must declare the table variable "@specsAndModel".

After searching around, I think this might be related to the execution context, but I haven't been able to resolve the problem.

Is it even possible for me to use a table variable in a call to the execute function?

Taryn · Accepted Answer · 2012-08-21 17:08:30Z

24

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.):

create table  ##specsAndModel 
(
    specName VARCHAR(50)
    ,specVal VARCHAR(50)
)
INSERT INTO ##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:

create table  #specsAndModel 
(
    specName VARCHAR(50)
    ,specVal VARCHAR(50)
)
INSERT INTO #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.):

create TABLE specsAndModel 
(
    specName VARCHAR(50)
    ,specVal VARCHAR(50)
)
INSERT INTO specsAndModel
VALUES('[modelNumber]', 'F00-B4R')

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

drop table 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 as table (
    specName VARCHAR(50)
    ,specVal VARCHAR(50)
 )
go
declare @t specsAndModel
insert @t VALUES('[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.

edited Aug 21, 2012 at 17:08

answered Aug 21, 2012 at 16:48

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Lamak Over a year ago

Actually, you can pass a table variable to sp_executesql, but you need to create a type for the variable. Take a look at this answer by Andomar: stackoverflow.com/a/7330410/519216

Aaron Bertrand Over a year ago

I would recommend against a global ##temp table (or a permanent table). Your first code sample works fine with a LOCAL #temp table and doesn't kill concurrency. P.S. I wrote the #temp / @table article you linked to. :-)

Taryn Over a year ago

@Lamak updated my answer to include that process. I was not aware of it.

tlehman Over a year ago

I like the use of sp_executesql better too, I have to drop the global temp table every time I use it.

Aaron Bertrand Over a year ago

I would take out the global and permanent table recommendations, or at least include a disclaimer about how these things will break if two users try to run this code at the same time. The #temp table and table variable methods don't have this problem.

|

Waqar Janjua · Accepted Answer · 2012-08-21 17:01:01Z

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)

edited Aug 21, 2012 at 17:01

answered Aug 21, 2012 at 16:49

Waqar Janjua

6,1432 gold badges28 silver badges36 bronze badges

Collectives™ on Stack Overflow

How to execute a SQL String that references a table variable?

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related