Using Variable for Table Name

[ter79]

I have a table that is going to be created everyday via a DTS Package. The naming of the table is "TableName_3_23_2004. I keep getting an error message saying that I must declare the variable @TDATE. This is the code that I'm using.

DECLARE @TDate NVARCHAR(100)
SET @TDate = RTRIM('Table_' + RTRIM(CONVERT(CHAR(2),DATEPART(MONTH, GETDATE()))) +N'_' + CONVERT(CHAR(2), DATEPART(DAY, GETDATE())) + N'_' + CONVERT(CHAR(4),DATEPART(YEAR, GETDATE())))


SELECT d.id,
	tf.id
FROM ##TempTable
INNER JOIN 
 @TDATE AS tf
ON d.id = tf.id

Any help is appreciated. Thanks in advance!

 

[ArtieChoke]

You can't use a variable as a table name. You need to use dynamic SQL to build the select statement.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[indrahig]

try to use this:

Exec('SELECT d.id, tf.id '+
' FROM ##TempTable INNER JOIN '+@TDATE+' AS tf '+
'ON d.id = tf.id')

Hope It's help..

 

[JohnDTampaBay]

Also check out faq183-3132 for more info on Executing Dynamically Created T-SQL Strings. Good luck!

--Angel
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)