SELECT with variables...

[annieko]

Hi all,

I'm trying to write a stored procedure to retrieve some records, however the table names varies as it's date related. I need to get information from a table called document_0707 for this month and 3 months history, history_0706, history_0705 and history_0704.

So next month I'll need table "document_0708", "history_0707", and so on...

I gave up trying to work out the "0707" bits in SQL and did it in ASP, so I pass on the variables to the stored procedure like this: "spTotalbyBranch 0707, 0706, 0705, 0704"

In the stored procedure I tried to do..
DECLARE @tableName
@tableName = 'history_' + @lastmonth <--- this will be '0706'
SET @result = (SELECT ... FROM @tableName WHERE...)

However that's a 2005 feature and I'm running SQL 2000, and I can't think of another way to do this.

Any ideas?

Thanks millions,
Annie

[mattyblah]

use dynamic sql, either exec or sp_executesql.