导致语法错误的变量=MAX()

Question

下面有以下SQL，它将是一个较大SP的一部分。我收到一个语法错误，我认为这是由于在变量@SQL的字符串中执行时所选变量@ColumnMM包含MAX函数所致。例如，将第25行(@第1列(更改为SELECT@Column01="abcd01"FROM MonthlySummary工作正常。

如何最好地解决此问题？

IF OBJECT_ID('New_Report', 'U') IS NOT NULL
DROP TABLE New_Report
;
CREATE TABLE New_Report (
Area NVARCHAR(255)
, Division NVARCHAR(255)
)
;
DECLARE @Column01 VARCHAR(6)
DECLARE @Column02 VARCHAR(6)
DECLARE @Column03 VARCHAR(6)
DECLARE @Column04 VARCHAR(6)
DECLARE @Column05 VARCHAR(6)
DECLARE @Column06 VARCHAR(6)
DECLARE @Column07 VARCHAR(6)
DECLARE @Column08 VARCHAR(6)
DECLARE @Column09 VARCHAR(6)
DECLARE @Column10 VARCHAR(6)
DECLARE @Column11 VARCHAR(6)
DECLARE @Column12 VARCHAR(6)
;
SELECT @Column01 = MAX(reporting_year)+'01' FROM MonthlySummary
SELECT @Column02 = MAX(reporting_year)+'02' FROM MonthlySummary
SELECT @Column03 = MAX(reporting_year)+'03' FROM MonthlySummary
SELECT @Column04 = MAX(reporting_year)+'04' FROM MonthlySummary
SELECT @Column05 = MAX(reporting_year)+'05' FROM MonthlySummary
SELECT @Column06 = MAX(reporting_year)+'06' FROM MonthlySummary
SELECT @Column07 = MAX(reporting_year)+'07' FROM MonthlySummary
SELECT @Column08 = MAX(reporting_year)+'08' FROM MonthlySummary
SELECT @Column09 = MAX(reporting_year)+'09' FROM MonthlySummary
SELECT @Column10 = MAX(reporting_year)+'10' FROM MonthlySummary
SELECT @Column11 = MAX(reporting_year)+'11' FROM MonthlySummary
SELECT @Column12 = MAX(reporting_year)+'12' FROM MonthlySummary
;
DECLARE @SQL NVARCHAR(MAX)
;
SET @SQL = 'ALTER TABLE New_Report ADD ' + @Column01 + ' VARCHAR(6)';
EXEC sys.sp_executesql @SQL;

AND FYI--SELECT MAX(reporting_year(+'01'FROM MonthlySummary的结果为202001。reporting_year是MonthlySummary中的VARCHAR(4(，尽管我知道在使用聚合函数后这并不重要。

Answer 1

看看文档中的这个链接：

https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-identifiers?view=sql-server-ver15#常规标识符规则

基本上，不能将数字字符用作表名、列名等的第一个字符。

除此之外，我还看到了一个类型不匹配的问题。MAX(reporting_year)是一个数字，但'01'、'02'等是文本，因此需要强制转换才能将它们组合在一起。此外，无论是从编写方式还是服务器所做的工作来看，代码都是不必要的重复。没有必要在整个表上多次运行相同的聚合。

DECLARE @MaxYear AS VARCHAR(4)
SELECT @MaxYear = CAST(MAX(reporting_year) As varchar(4)) FROM MonthlySummary
DECLARE @Column01 VARCHAR(9) = QUOTENAME('_'+@MaxYear+'01')
DECLARE @Column02 VARCHAR(9) = QUOTENAME('_'+@MaxYear+'02')
DECLARE @Column03 VARCHAR(9) = QUOTENAME('_'+@MaxYear+'03')
DECLARE @Column04 VARCHAR(9) = QUOTENAME('_'+@MaxYear+'04')
DECLARE @Column05 VARCHAR(9) = QUOTENAME('_'+@MaxYear+'05')
DECLARE @Column06 VARCHAR(9) = QUOTENAME('_'+@MaxYear+'06')
DECLARE @Column07 VARCHAR(9) = QUOTENAME('_'+@MaxYear+'07')
DECLARE @Column08 VARCHAR(9) = QUOTENAME('_'+@MaxYear+'08')
DECLARE @Column09 VARCHAR(9) = QUOTENAME('_'+@MaxYear+'09')
DECLARE @Column10 VARCHAR(9) = QUOTENAME('_'+@MaxYear+'10')
DECLARE @Column11 VARCHAR(9) = QUOTENAME('_'+@MaxYear+'11')
DECLARE @Column12 VARCHAR(9) = QUOTENAME('_'+@MaxYear+'12')

最后，作为一个好的实践，我反对这样命名列。如果你想要数据透视，那没关系，但让你的客户端工具担心名称，并根据需要进行调整。这样做，很可能会显著地简化整个过程，可能只需要一个SELECT语句。