SQL Server 2008 SELECT * FROM @variable?

Question

Maybe?

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".

Answer 1

Short answer: None.

Long answer: Noooooooooooooooooooooooooooooooooooooooooooo. Use dynamic SQL if necessary, but if you structure your tables so that you don't know the table name before, you might need to rethink your schema.

Here's a great resource for learning how to use dynamic SQL: The Curse and blessings of dynamic SQL

Answer 2

if you are trying to select this name from the table, you can do something like this:

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

Answer 3

my solution for this:

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

Answer 4

Different people seem to interpret OP differently.

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

"Put the table name in the variable, and then use this variable as if it were the name of the table."

 DECLARE @TableIWantRecordsFrom varchar(50) -- ^^^^^^^^^^^^^^^^^^^^^^ SET @TableIWantRecordsFrom = (SELECT TableName FROM qms_Types WHERE Id = 1) -- (L1) -- ^^^^^^^^^^^^^^^^^^^^^^ -- Let 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 all the answers and answers it is clear that this kind of maneuver cannot be performed - unless you are using dynamic SQL, which ...

• it can be a little painful to create and maintain and
• there may be more work to create than the time that it will “save” you in the future.

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

There are other languages where this can be done ... literally two lines of code (see (L1) and (L2) in the above code) and should not do much formatting and editing.)

(I did this before - there is another language where all you need is L1 and L2 ...)

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

Unfortunately, SQL Server will not do this without a decent effort ...

• write your SQL first, then
• check it to make sure it really works then
• frame each line with tag marks, and then select your ticks, which are now within the THOSE tick marks.
• declare a variable
• set the variable to the sql statement that you noted above.
• (Maybe I am missing some extra steps)
• Oh, and then if you ever need to support him
• you also need to be very careful and just edit it right there as it is, and hope that you get everything in order - or ... maybe you saved a copy of it. un-ticked and un-variablized so you can edit the "real" sql, and then when you are done, you can complete these steps again ... again.

Answer 5

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 

Answer 6

The only way to do this is to use Dynamic SQL, which refers to the practice of creating T-SQL text and executing it using sp_executesql (or just exec )

Here is a useful link about dynamic sql Curse and blessings of dynamic SQL .

You really need to think about whether this applies to dynamic sql or if there is another way to perform this operation.