CODEWITHSUNDEEP
sql-server-2008variables
soamazing
soamazing

Reputation: 1696

SQL Server 2008 SELECT * FROM @variable?

It is possible?

DECLARE @vTableName varchar(50)

SET @vTableName = (SELECT TableName FROM qms_Types WHERE Id = 1)

SELECT * FROM @vTableName

I have this error:

Msg 1087, Level 16, State 1, Line 3 Must declare the table variable "@vTableName".

Upvotes: 12

Views: 82213

Answers (6)

user2792497
user2792497

Reputation: 139

It seems as though different folks are interpreting the OP differently.

I'm pretty sure the OP is asking for this type of concept / ability / maneuver...

"Put a table name into a variable and then use that variable as though it were a table name."

DECLARE @TableIWantRecordsFrom varchar(50)
--      ^^^^^^^^^^^^^^^^^^^^^^

SET @TableIWantRecordsFrom = (SELECT TableName FROM qms_Types WHERE Id = 1) -- (L1)
--  ^^^^^^^^^^^^^^^^^^^^^^

-- Let's say, at this point, @TableIWantRecordsFrom ... contains the text 'Person'
--                           ^^^^^^^^^^^^^^^^^^^^^^

-- assuming that is the case then...
-- these two queries are supposed to return the same results:

SELECT top 3 fname,lname,mi,department,floor FROM Person
--                                                ^^^^^^
SELECT top 3 fname,lname,mi,department,floor FROM @TableIWantRecordsFrom -- (L2)
--                                                ^^^^^^^^^^^^^^^^^^^^^^

From reading all the responses and answers, it appears that this kind of maneuver can't be done - unless - you use dynamic SQL which...

  • can be a bit of a pain to create and maintain and
  • can be more work to create than the time it "saves" you in the future.

================================================================

There are other languages where this can be done... in literally, two lines of code (see (L1) and (L2) in above code) and not having to do a lot of formatting and editing.)

(I've done it before - there is another language where all you'd need is L1 and L2...)

================================================================

It is unfortunate that SQL Server will not do this without going to a decent amount of effort...

  • first write your SQL then
  • test it to make sure it does, in fact, work then
  • frame each line with tick marks and then escape your ticks that are now inside THOSE tick marks
  • declare the variable
  • set the variable to the sql statement you ticked above
  • (I may be missing some additional steps)
  • Oh, and then, if you ever need to maintain it
  •     you need to either, be very careful and just edit it right there, as is, and hope you get it all just right -or- you may have saved a copy of it... un-ticked and un-variablized so you can edit the "real" sql and then when you're done you can RE DO these steps... again.

Upvotes: 3

Iago Correia Guimarães
Iago Correia Guimarães

Reputation: 141

my solution for this:

EXECUTE('SELECT * FROM  ' + TableName + '')

Upvotes: 2

HeavenCore
HeavenCore

Reputation: 7683

if you're trying to select from a table of that name, then you can do something like this:

DECLARE @vTableName varchar(50) 
SET @vTableName = (SELECT TableName FROM qms_Types WHERE Id = 1) 
EXECUTE('SELECT * FROM [' + @vTableName + ']')

Upvotes: 5

Taryn
Taryn

Reputation: 247860

The only way you can do this is through Dynamic SQL which refers to the practice of creating a T-SQL text and executing it using the sp_executesql (or simply exec)

Here is a helpful link about dynamic sql The Curse and Blessings of Dynamic SQL.

You should really think whether or not this is a case for dynamic sql or if there is another way for you to perform this operation.

Upvotes: 1

Diego
Diego

Reputation: 36166

I think you want this:

DECLARE @vTableName table(TableName varchar(50))

insert into @vTableName 
SELECT TableName FROM qms_Types WHERE Id = 1

SELECT * FROM @vTableName

Upvotes: 1

Jason
Jason

Reputation: 1325

Short answer: No.

Long answer: Noooooooooooooooooooooooooooooooooooooo. Use dynamic SQL if you have to, but if you're structuring your tables in a way where you don't know the table name ahead of time, it might benefit you to rethink your schema.

Here is a great resource for learning how to use dynamic SQL: The Curse and Blessings of Dynamic SQL

Upvotes: 22

Related Questions