469,898 Members | 1,720 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,898 developers. It's quick & easy.

slecting a table using a local variable name

I am new at sql so would appreciate some help
I have the name of a table in alocal variable is it possible to select this
table
DECLARE @name sysname

SET @name = 'tblSniffedItems'

PRINT @name

SELECT * FROM @name

I expected this wo work but I got the following error.
@name is declare as far as I know

Server message

Must declare the variable '@name'.

Thanks in advance

Andre
Jul 23 '05 #1
4 5845
Yes it is, although not quite in the way you are attempting.

Here is an article by Erland Sommarskog covering precisely this topic.
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dynamic_sql.html#Dyn_table (select * from
@tablename)

Mr Tea

"Andre Arpin" <ar***@kingston.net> wrote in message
news:Vc********************@wtccommunications.ca.. .
I am new at sql so would appreciate some help
I have the name of a table in alocal variable is it possible to select
this table
DECLARE @name sysname

SET @name = 'tblSniffedItems'

PRINT @name

SELECT * FROM @name

I expected this wo work but I got the following error.
@name is declare as far as I know

Server message

Must declare the variable '@name'.

Thanks in advance

Andre

Jul 23 '05 #2
Andre Arpin (ar***@kingston.net) writes:
I am new at sql so would appreciate some help I have the name of a table
in alocal variable is it possible to select this table
DECLARE @name sysname

SET @name = 'tblSniffedItems'

PRINT @name

SELECT * FROM @name

I expected this wo work but I got the following error.
@name is declare as far as I know

Server message

Must declare the variable '@name'.


The error message is somewhat misleading. @name is indeed declared,
but the declaration is not appropriate for the context. This would
be:

DECLARE @tbl TABLE (a int NOT NULL)

INSERT @tbl (a) VALUES (12)

SELECT * FROM @tbl

That is. @tbl is a table variable. A table variable is similar to a
temp table, those of which the name starts with #. There are some
subtle differences between them, but I will not go into that now.

As for doing what you trying to do, the answer is that this is something
you normally don't do. A table is very much a singular item, which in
the general case is different from all tables. And even if two tables
are structurally similar, that is have the same columns etc, they are
are still two individual items as far as the optimizer is concerned.

SELECT * FROM a WHERE col = 1
SELECT * FROM b WHERE col = 1

may yield two different query plans, because the data distribution is
different. For one table using a non-clustered index on col is a good
idea, but be a bad idea for the other.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3
Do not write SQL like this. It violates basic software engineering
principles about cohesion in modules. That is more fundamental than
just SQL.

Jul 23 '05 #4
How about a utility module that takes a given table and a filter clause as
text string, then procedes to exececute the filter clause against that table
and generates the set of INSERT scripts to re-create the data matching the
filter clause.

This would be a legitimate(and extremely useful in the context of this
forum) use for such a piece of SQL, wouldnt you agree?

Mr Tea

"--CELKO--" <jc*******@earthlink.net> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Do not write SQL like this. It violates basic software engineering
principles about cohesion in modules. That is more fundamental than
just SQL.

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by dzieciou | last post: by
1 post views Thread by luvdairish | last post: by
2 posts views Thread by rn5a | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.