Trouble with select statement using a variable 1

[hrhadin]

I'm trying to open a cursor using an SQL statement based on a DB whose name changes from month to month. I have the following code:

SET @dProcDate = '02-01-2007 00:00:00'
SET @cMonth = 'Test_' + CASE WHEN MONTH(@dProcDate) < 10 THEN '0' + CAST(MONTH(@dProcDate) AS char(1)) ELSE CAST(MONTH(@dProcDate) AS char(2)) END +
CAST(YEAR(@dProcDate) AS char(4)) + '.Dbo.SomeTable'

DECLARE cur3011 CURSOR LOCAL FAST_FORWARD
FOR
SELECT cAgency
FROM [@cMonth]

open cur3011

I'm receiving the error 'Invalid Object @cMonth'

Does anybody know of a way to accomplish this?

 

[arrowhouse]

How is your variable @cMonth declared? is it a table variable?

 

[hrhadin]

It's a variable. I left out the Declare statement for simplicity.

 

[SQLDenis]

2 things

1 dynamic SQL is the way for this

2) this
SET @cMonth = 'Test_' + CASE WHEN MONTH(@dProcDate) < 10 THEN '0' + CAST(MONTH(@dProcDate) AS char(1)) ELSE CAST(MONTH(@dProcDate) AS char(2)) END +
CAST(YEAR(@dProcDate) AS char(4)) + '.Dbo.SomeTable'

can be replaced with this
SET @cMonth = 'Test_' + right(replace(convert(varchar(10),@dProcDate,105),'-',''),6)+ '.Dbo.SomeTable'

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[arrowhouse]

In which case you declare becomes

DECLARE cur3011 CURSOR LOCAL FAST_FORWARD
FOR
SELECT @cMonth

but that means you just get one value and I'm not sure why you would be cursoring in that case.

 

[SQLDenis]

what you need is this

exec ('SELECT cAgency
FROM ' + @cMonth)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[arrowhouse]

Sorry (should read more carefully). I get it - you are trying to select from the table name you have put into variable and SLQDenis is right.

 

[hrhadin]

Thanks to SQLDenis for your help. Your tips allowed me to complete the stored procedure I was working on.