将变量放入变量

Question

我有一些每月运行的代码，因此为了更容易，我更改了整个存储过程以处理变量。这样，我就可以在页面的顶部修改它们，而不必在整个页面中搜索并进行更改。

然而，月份是数字的，我开始想，我是否可以做一个更改，让变量使用月份号(这将是一个变量)。

因此，我做了修改，代码片段如下所示：

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

现在我收到了大量的错误，所有这些都是：

必须声明标量变量"@MyVariableName“

我假设这与我的声明和SET语句的顺序有关？有人能帮我解决这个问题吗？

Answer 1

删除go语句，并在sp_rename前面加上exec .就像下面。之所以如此，是因为变量只在语句的持续时间内存在，而go表示语句的结束。因此，go之后的所有内容都是新语句，如果要再次使用它们，就需要声明其中使用的变量。

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