Delete SQL Table using a variable that refers to the Table Name

[LVande]

SQLLY challenged be gentle --

Trying to create code that will drop a table using a variable as the
Table Name.

DECLARE @testname as char(50)
SELECT @testname = 'CO_Line_of_Business_' +
SUBSTRING(CAST(CD_LAST_EOM_DATE
AS varchar), 5, 2) + '_' + LEFT(CAST(CD_LAST_EOM_DATE AS varchar),
4)+ '_' + 'EOM'
FROM TableName

Print @testname = 'blah...blah...blah' (which is the actual table
name on the server)

How can I use this variable (@testname) to drop the table? Under
severe time constraints so any help would be greatly appreciated.

[David Portas]

SET @testname = 'DROP TABLE ['+@testname+']'
EXEC (@testname)

From your naming convention it looks like you have some tables that contain
sets of data based on dates. If so then I suggest you revise your design.
Why not just leave the data in one table with a date column and avoid this
kind of messy dynamic SQL manipulation?

--
David Portas
SQL Server MVP
--

[BUSHII]

DECLARE @TestName VarChar(50)
DECLARE @vQString VarChar(8000)

SET @TestName = 'MyTmpTable'
SET @vQString =
'IF object_id('''+@TestName+''') IS NOT NULL '+ /* if table @TestName
exist -> then drop this table */
'DROP TABLE '+@TestName

Exec (@vQString)
ps.
you shoulnd use Char(50). In this cas better is VarChar(50).

Best Regards
Piotr Leib
Uzytkownik "LVande" <lv********@fhlbdm.com> napisal w wiadomosci
news:6b*************************@posting.google.co m...

SQLLY challenged be gentle --

Trying to create code that will drop a table using a variable as the
Table Name.

DECLARE @testname as char(50)
SELECT @testname = 'CO_Line_of_Business_' +
SUBSTRING(CAST(CD_LAST_EOM_DATE
AS varchar), 5, 2) + '_' + LEFT(CAST(CD_LAST_EOM_DATE AS varchar),
4)+ '_' + 'EOM'
FROM TableName

Print @testname = 'blah...blah...blah' (which is the actual table
name on the server)

How can I use this variable (@testname) to drop the table? Under
severe time constraints so any help would be greatly appreciated.