Putting a variable into a variable

Question

I've got some code that I run every month, so to make it easier I've changed the entire stored procedure to work with variables. This way, I could just change them at the top of the page instead of having to hunt through the whole thing and make changes.

However, the months are numerical and I started wondering if I can just make one change and have the variables use the month number (which will be a variable).

So I made the change and my code snippet looks like this:

DECLARE @RptYear VarChar(4)
DECLARE @RptMth VarChar(2)
DECLARE @PrevRptMth VarChar(2)
SET @RptYear = '2015'
SET @RptMth = '09'
SET @PrevRptMth = '08'

--  Do not edit anything below this line --
--Rename the tables from previous month to current month
DECLARE @CAA_PMHS_AO_Old as VARCHAR(20)
DECLARE @CAA_PMHS_AU_Old as VARCHAR(20)
DECLARE @CAA_PROC_AO_Old as VARCHAR(20)
DECLARE @CAA_PROC_AU_Old as VARCHAR(20)
DECLARE @CAA_PMHS_AO_New as VARCHAR(20)
DECLARE @CAA_PMHS_AU_New as VARCHAR(20)
DECLARE @CAA_PROC_AO_New as VARCHAR(20)
DECLARE @CAA_PROC_AU_New as VARCHAR(20)

DECLARE @RptDate VarChar(10)
DECLARE @PrtMsg as VARCHAR(8000)
DECLARE @DayTime as DATETIME
DECLARE @ProcOver as VARCHAR(20)
DECLARE @ProcUnder as VARCHAR(20)
DECLARE @PMHSOver as VARCHAR(20)
DECLARE @PMHSUnder as VARCHAR(20)
DECLARE @QRY1 as VARCHAR(max)
DECLARE @QRY2 as VARCHAR(max)
DECLARE @QRY3 as VARCHAR(max)
DECLARE @QRY4 as VARCHAR(max)

SET @CAA_PMHS_AO_Old = 'CAA_PMHS_AO_' + @PrevRptMth
SET @CAA_PMHS_AO_New = 'CAA_PMHS_AO_' + @RptMth
SET @CAA_PMHS_AU_Old = 'CAA_PMHS_AU_' + @PrevRptMth
SET @CAA_PMHS_AU_New = 'CAA_PMHS_AU_' + @RptMth

SET @CAA_PROC_AO_Old = 'CAA_PROC_AO_' + @PrevRptMth
SET @CAA_PROC_AO_New = 'CAA_PROC_AO_' + @RptMth
SET @CAA_PROC_AU_Old = 'CAA_PROC_AU_' + @PrevRptMth
SET @CAA_PROC_AU_New = 'CAA_PROC_AU_' + @RptMth

go
sp_rename @CAA_PMHS_AO_Old, @CAA_PMHS_AO_New
go
sp_rename @CAA_PMHS_AU_Old, @CAA_PMHS_AU_New
go
sp_rename @CAA_PROC_AO_Old, @CAA_PROC_AO_New
go
sp_rename @CAA_PROC_AU_Old, @CAA_PROC_AU_New
go

--Change the Report Date and table names to reflect the month you are running
SET @RptDate = @RptYear + '-' + @RptMth
SET @ProcOver = 'dbo.CAA_PROC_AO_' + @RptMth
SET @ProcUnder = 'dbo.CAA_PROC_AU_' + @RptMth
SET @PMHSOver = 'dbo.CAA_PMHS_AO_' + @RptMth
SET @PMHSUnder = 'dbo.CAA_PMHS_AU_' + @RptMth

Now I'm getting a ton of errors, all of which are:

Must declare the scalar variable "@MyVariableName"

I'm assuming this has something to do with the order of my DECLARE and SET statements? Can someone help me fix this?

Answer 1

Remove the go statements and precede the sp_rename with exec ... like below. The reason for this is because variables live only for the duration of a statement and the go signifies the end of a statement. Everything after the go is therefore a new statement and variables used therein needs to be declared if they are to be used again.

DECLARE @RptYear VarChar(4)
DECLARE @RptMth VarChar(2)
DECLARE @PrevRptMth VarChar(2)
SET @RptYear = '2015'
SET @RptMth = '09'
SET @PrevRptMth = '08'

--  Do not edit anything below this line --
--Rename the tables from previous month to current month
DECLARE @CAA_PMHS_AO_Old as VARCHAR(20)
DECLARE @CAA_PMHS_AU_Old as VARCHAR(20)
DECLARE @CAA_PROC_AO_Old as VARCHAR(20)
DECLARE @CAA_PROC_AU_Old as VARCHAR(20)
DECLARE @CAA_PMHS_AO_New as VARCHAR(20)
DECLARE @CAA_PMHS_AU_New as VARCHAR(20)
DECLARE @CAA_PROC_AO_New as VARCHAR(20)
DECLARE @CAA_PROC_AU_New as VARCHAR(20)

DECLARE @RptDate VarChar(10)
DECLARE @PrtMsg as VARCHAR(8000)
DECLARE @DayTime as DATETIME
DECLARE @ProcOver as VARCHAR(20)
DECLARE @ProcUnder as VARCHAR(20)
DECLARE @PMHSOver as VARCHAR(20)
DECLARE @PMHSUnder as VARCHAR(20)
DECLARE @QRY1 as VARCHAR(max)
DECLARE @QRY2 as VARCHAR(max)
DECLARE @QRY3 as VARCHAR(max)
DECLARE @QRY4 as VARCHAR(max)

SET @CAA_PMHS_AO_Old = 'CAA_PMHS_AO_' + @PrevRptMth
SET @CAA_PMHS_AO_New = 'CAA_PMHS_AO_' + @RptMth
SET @CAA_PMHS_AU_Old = 'CAA_PMHS_AU_' + @PrevRptMth
SET @CAA_PMHS_AU_New = 'CAA_PMHS_AU_' + @RptMth

SET @CAA_PROC_AO_Old = 'CAA_PROC_AO_' + @PrevRptMth
SET @CAA_PROC_AO_New = 'CAA_PROC_AO_' + @RptMth
SET @CAA_PROC_AU_Old = 'CAA_PROC_AU_' + @PrevRptMth
SET @CAA_PROC_AU_New = 'CAA_PROC_AU_' + @RptMth

exec sp_rename @CAA_PMHS_AO_Old, @CAA_PMHS_AO_New
exec sp_rename @CAA_PMHS_AU_Old, @CAA_PMHS_AU_New
exec sp_rename @CAA_PROC_AO_Old, @CAA_PROC_AO_New
exec sp_rename @CAA_PROC_AU_Old, @CAA_PROC_AU_New


--Change the Report Date and table names to reflect the month you are running
SET @RptDate = @RptYear + '-' + @RptMth
SET @ProcOver = 'dbo.CAA_PROC_AO_' + @RptMth
SET @ProcUnder = 'dbo.CAA_PROC_AU_' + @RptMth
SET @PMHSOver = 'dbo.CAA_PMHS_AO_' + @RptMth
SET @PMHSUnder = 'dbo.CAA_PMHS_AU_' + @RptMth