Variable name in FROM clause

Question

Variable name in FROM clause

shache

Old Hand

Points: 366
More actions
February 20, 2003 at 9:06 am

#81127

Is it possible to have a variable name in the FROM clause of a SELECT statement.
For example I want to write a stored procedure that will select the same columns from two tables (current and history), instead of writting the same stored procedure twice I would like to have a variable in the FROM and pass the parameter when it is called.
Thank you
Stéphane

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 1

you could always use an exec('select <whatever> from ' + @tableName), but I'd not be surprised to hear there are performance issues with this strategy, right guys?

Crispin Proctor SSCoach Points: 18161 More actions · Answer 2

Is there any relation between the to tables?

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Crispin Proctor SSCoach Points: 18161 More actions · Answer 3

The easiest and fastest I would think (not knowing if the tables are related) is to have an IF statement around the two selects.

If @CurrentID > -1 OR IS NOT NULL

Begin

Select From Current

End

Else

Begin

Select From History.

End

Edited by - crappy on 02/20/2003 1:06:55 PM

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

philcart SSC-Forever Points: 47794 More actions · Answer 4

Writing a stored procedure with an IF statement will provide you with the most optimal execution in terms of time and resources.

Using Dynamic SQL will lead to things like stored procedure recompiles, etc... which will impact on performance.

Thanks

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

mromm SSCarpal Tunnel Points: 4301 More actions · Answer 5

IF you have to fit in one statement, here is how:

DECLARE @current bit

SET @current = 1 -- or 0

SELECT ... FROM current_table

WHERE @current = 1

UNION

SELECT ... FROM history_table

WHERE @current = 0

gregj Old Hand Points: 346 More actions · Answer 6

Question to MSSQL gurus about mromm's answer...

Using UNION instead of UNION ALL causes MSSQL to sort the results to remove duplicate rows. However, the WHERE condition of the 2 SELECT's will keep that from happening. Will MSSQL be smart enough not to sort the results anyway?

Thanks,

Greg

guarddata SSCertifiable Points: 6583 More actions · Answer 7

Greg,

No - you are correct in that the UNION causes the duplicates to be removed. This is true even if all the results come from a single result set. In your example, if @current = 0, the result would be identical to

SELECT DISTINCT ... FROM history_table

Guarddata-