当我尝试在Visual Studio中执行此MS SQL Server存储过程时(在服务器资源管理器中创建后),我得到,"Msg 102,Level 15,State 1,Procedure duckbilledPlatypi,Line 21‘SUM’附近的语法不正确。":
CREATE PROCEDURE [dbo].[duckbilledPlatypi]
@Unit varchar(25),
@BegDate datetime,
@EndDate datetime
AS
DECLARE
@Week1End datetime,
@Week2begin datetime
Select Description,
@BegDate BegDate,
@Week1End Week1End,
@Week1End Week2Begin,
@EndDate EndDate,
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Usage ELSE 0 END) Week1Usage,
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) Week2Usage,
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) Week1Price,
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Price ELSE 0 END) -
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Usage ELSE 0 END) UsageVariance
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) -
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) PriceVariance,
(SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) -
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) )
/ SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) PercentageOfPriceVariance
From InvoiceDetail Ind
Where Ind.Unit = @Unit
AND @Ind.InvoiceDate BETWEEN @BegDate AND @EndDate
问题线是:
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) -
在上下文中,整个语句部分是:
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) -
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) PriceVariance,
我想也许我需要把声明封装在另一对括号里,就像这样:
*(SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) -
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END)) PriceVariance,*
但我仍然得到同样的错误消息。
更新
有了这个(HoneyBadger的答案):
CREATE PROCEDURE [dbo].[variancePriceByProductWithPriceChangePercentage]
@Unit varchar(25),
@BegDate datetime,
@EndDate datetime
AS
DECLARE
@Week1End datetime,
@Week2begin datetime
Select Description,
@BegDate BegDate,
@Week1End Week1End,
@Week1End Week2Begin,
@EndDate EndDate,
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Usage ELSE 0 END)
Week1Usage,
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END)
Week2Usage,
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END)
Week1Price,
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Price ELSE 0 END) -
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Usage ELSE 0 END)
UsageVariance,
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) -
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END)
PriceVariance,
(SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) -
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) )
/ SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END)
PercentageOfPriceVariance
From InvoiceDetail Ind
Where Ind.Unit = @Unit
AND Ind.InvoiceDate BETWEEN @BegDate AND @EndDate
Group By Description,
@BegDate,
@Week1End,
@Week1End,
@EndDate
我得到以下指法:
消息164,级别15,状态1,过程变量PriceByProductWithPriceChangePercentage,第30行每个GROUP BY表达式必须至少包含一个不是外部引用的列。消息207,级别16,状态1,过程变量PriceByProductWithPriceChangePercentage,第16行列名"Usage"无效。消息207,级别16,状态1,过程变量PriceByProductWithPriceChangePercentage,第17行列名"Usage"无效。消息207,级别16,状态1,过程变量PriceByProductWithPriceChangePercentage,第20行列名"Usage"无效。消息207,级别16,状态1,过程变量PriceByProductWithPriceChangePercentage,第21行列名"Usage"无效。消息207,级别16,状态1,过程变量PriceByProductWithPriceChangePercentage,第23行列名"Usage"无效
类型:
SUM(CASE [..snip...][ END) UsageVariance
^--missing comma
这就是为什么在下一行的SUM
上出现语法错误的原因。您基本上有无效的CCD_ 2。
正如我在评论中所说,您应该添加一个group by
。其他人指出你有一个缺失的逗号,我也补充道:
Select Description,
@BegDate BegDate,
@Week1End Week1End,
@Week1End Week2Begin,
@EndDate EndDate,
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Usage ELSE 0 END) Week1Usage,
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) Week2Usage,
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) Week1Price,
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Price ELSE 0 END) -
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Usage ELSE 0 END) UsageVariance,
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) -
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) PriceVariance,
(SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Usage ELSE 0 END) -
SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) )
/ SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) PercentageOfPriceVariance
From InvoiceDetail Ind
Where Ind.Unit = @Unit
AND Ind.InvoiceDate BETWEEN @BegDate AND @EndDate
Group By Description
edit:显然group by
中的变量是错误的