T-SQL Variables

[DWalker]

Why can't a variable (an expression) be used as a table name? This T-
SQL gives an error:

Declare @Tablename varchar(20)
Set @Tablename = 'Positions'
Select top 10 * from @Tablename

I get the message:

Server: Msg 137, Level 15, State 2, Line 3
Must declare the variable '@Tablename'.

Duh, the variable IS declared. The error message is wrong.

I know this works:

Declare @Parm Varchar(20)
Set @Parm = 'Bob'
Select * from SomeTable where FName = @Parm

Bah. Variables should be allowed anywhere, and replaced with their
values! T-SQL isn't a complete parsed language, I don't suppose.

David Walker

[Keith Kratochvil]

Check this excellent article for the explanation:
http://www.sommarskog.se/dynamic_sql.html

--
Keith

"DWalker" <None> wrote in message
news:eyKjmWMz...@TK2MSFTNGP12.phx.gbl...

[Steve Kass]

The error message is wrong, but what it probably means is that
@Tablename is not declared as a table variable. Since SQL Server 2000
allows variables to represent tables, you could have

declare @T table (i int)
select * from @T

The reason a variable can't be used as a table name is that a table name
is not a piece of data - it's metadata from the database schema. There
are a lot of things (all of them not data) that variables can't be used
for. You can't do this, for example:

declare @operator varchar(2)
set @operator = '>='
select * from Northwind..Orders
where OrderID @operator 11000

There are some places where the distinction between data and metadata is
blurred, but in general, if you don't know the source of your data when
you write a query, it's more than likely there is something wrong with
your data model.

Steve Kass
Drew University

[David Portas]

The real question is why would you need a variable to reference a table name.
This is a valid thing to do for DBA-type admin tasks and there are solutions
to that problem. In an application it shouldn't be necessary to reference
tables in that way. At least not if you stick to a proper relational design
and avoid treating tables as objects.

--
David Portas
SQL Server MVP
--

[DWalker]

=?Utf-8?B?RGF2aWQgUG9ydGFz?= <REMOVE_BEFORE_R...@acm.org>
wrote in news:C6397B4C-AA64-4618...@microsoft.com:

Right, for your comments and Steve's, I'm not doing this in an application;
I'm creating a partitioned view and making all the sub-tables. It's
definitely a DBA admin task.

David Walker