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.

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
--

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" <lvandehaar@fhlbdm.com> napisal w wiadomosci
news:6b4d0472.0407141010.85ec0bf@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.