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