Drop table @variable problems - 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

SQL Server 2000 Forums

SQL Server Administration (2000)

Drop table @variable problems

Author

Topic

steve_aspfree
Starting Member

1 Post

Posted - 2002-09-09 : 09:55:05

I have a script that drops a detail table that is older than X number of days. Here is the script It bombs on the drop table path. If I do a print on the @tn variable and copy and paste that with the drop table ALL_Data912002 that works and drops the table. I've tried loggin in as domain admin, SA nothing. Am I missing something?

Declare @e datetime
Declare @tn varchar(20)
set @e = getdate() - 8
set @tn = RTrim('AllData_' + CAST(month(@e) AS varchar(10)) + CAST(day(@e) AS varchar(10)) + CAST(year(@e) AS varchar(10)))
Drop table @tn

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-09-09 : 10:00:08

The DROP TABLE command does not take a variable. You need to do the whole thing as dynamic sql.


Declare @e datetime 
Declare @tn varchar(20) 
set @e = getdate() - 8 
set @tn = RTrim('AllData_' + CAST(month(@e) AS varchar(10)) + CAST(day(@e) AS varchar(10)) + CAST(year(@e) AS varchar(10)))
set @tn = 'drop table ' + @tn
exec(@tn)

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources