Setting table as variable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Setting table as variable

Author

Topic

Lionheart
Starting Member

41 Posts

Posted - 2009-04-21 : 11:04:17

I need to run three separate select queries on the same table (once each quarter) so thought I would make the table name a variable and then refer to this in the from statement, but it gives me an error.

I have the code below (simplified slightly). How can I change it to get it to work?

declare @Table as varchar (10)

set @Table='table_name'

select info, data, sum(costs)
from @table

The error says "Must declare the table variable "@Table". I thought I had.

Thx, LH

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-21 : 11:11:39

You cannot execute dynamic sql with compiled.

You can do this instead:
DECLARE @table as VARCHAR(10)
DECLARE @sql as VARCHAR(10)
SET @table = 'table_name'

SET @sql = 'SELECT info, data, SUM(costs)
from ' + @table;
sp_exec(@sql);

Just so you know, the difference between compiled and dynamic sql is insignificant in modern database engines.

hi2sunil
Starting Member

3 Posts

Posted - 2009-04-21 : 11:13:30

try this

declare @Table as varchar (10)

set @Table='table_name'
Exec ('select info, data, sum(costs) from ' + @table + ' group By Info, Data ')

Thanks
Sunil Kovvur

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-22 : 03:53:33

Make sure you read this
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 04:01:54

quote:
Originally posted by whitefang

sp_exec(@sql)

What is sp_exec ?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 04:04:57

quote:
Originally posted by whitefang

Just so you know, the difference between compiled and dynamic sql is insignificant in modern database engines.

VERY interesting.
Do you have a link or anything to back up that statement?

Because I recently read this article written by a Microsoft SQL Server MVP (8 consecutive years)
http://www.sommarskog.se/dynamic_sql.html#queryplans

E 12°55'05.63"
N 56°04'39.26"

pootle_flump

1064 Posts

Posted - 2009-04-22 : 07:29:18

quote:
Originally posted by whitefang

Just so you know, the difference between compiled and dynamic sql is insignificant in modern database engines.

More accurately you are describing the difference between compiled and not compiled SQL - whether or not it is dynamically constructed is irrelevant. And the difference between compiled and not compiled SQL is dependent on the complexity of the query - the more complex the query, the more complex the plans the database engine must consider.

The query in this example is trivial and probably won't even result in a cached plan, so you are correct with regard to the OP's specific case, however this does not mean you can extend your assertion to all cases.

pootle_flump

1064 Posts

Posted - 2009-04-22 : 07:38:50

quote:
Originally posted by Lionheart

I need to run three separate select queries on the same table (once each quarter) so thought I would make the table name a variable and then refer to this in the from statement, but it gives me an error.

I'm also struggling to see why using a variable for a table name is required. If you executed one query on three tables there might at least be some reasoning, but three queries on one table....
What are you gaining by using a variable for your table name?

Lionheart
Starting Member

41 Posts

Posted - 2009-04-22 : 08:12:29

quote:
Originally posted by pootle_flump

quote:
Originally posted by Lionheart

I need to run three separate select queries on the same table (once each quarter) so thought I would make the table name a variable and then refer to this in the from statement, but it gives me an error.
I'm also struggling to see why using a variable for a table name is required. If you executed one query on three tables there might at least be some reasoning, but three queries on one table....
What are you gaining by using a variable for your table name?

Because in the end I would like to feed the variable from an external source such as an Excel sheet cell, that will allow the procedure to automatically put the table name into the queries.

pootle_flump

1064 Posts

Posted - 2009-04-22 : 08:14:48

But if it only acts on one table, then that variable will always be the same. So there is no point "parameterising" it. Or do you mean you apply one query to many tables (not the many queries to one table you have said)?

Lionheart
Starting Member

41 Posts

Posted - 2009-04-22 : 08:33:05

quote:
Originally posted by pootle_flump

But if it only acts on one table, then that variable will always be the same. So there is no point "parameterising" it. Or do you mean you apply one query to many tables (not the many queries to one table you have said)?

Many queries to one table. How else would you go about doing that without having to change the code in each from statement?

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-22 : 08:45:26

You mean you have multiple tables you pull data from?

pootle_flump

1064 Posts

Posted - 2009-04-22 : 09:37:53

quote:
Originally posted by Lionheart
Many queries to one table. How else would you go about doing that without having to change the code in each from statement?

The point is that the one things that is static here is the table. Put it another way, it sounds like you mean this:

SELECT col1, col2
FROM myTable
WHERE col3 = 'x'

SELECT DISTINCT col4
FROM myTable

SELECT MAX(col5), col6
FROM myTable
GROUP BY col6

This is many (three) queries to one table. The only thing that is common between all of them is the table name, so this seems the last thing you would want to parameterise.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources