SQL temp table creation help

[s2001]

I am getting error when i run the following sql query:

Declare @tempTable varchar(50)
select @tempTable= '#' + @userName
DROP TABLE @tempTable

Error:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '@tempTable'.

TIA

Thanks,
MB

 

[SQLDenis]

Declare @tempTable varchar(50)
select @tempTable= '#' + @userName
exec ('DROP TABLE ' + @tempTable )

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[mrdenny]

The reason that Denis's code works while your doesn't is that SQL Server doesn't allow object names to be variables.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[s2001]

Thanks denis. Can we do it without using the exec command?
Becuase i have create table, insert into and select statement on the the temp table following the drop command.

TIA

Thanks,
MB

 

[SQLDenis]

no

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions