SQL Server:在标量上使用IF执行过程

Question

我有一个查询，可以逐行比较两列中的值，并将ABS(diff)求和为单个标量int。我想使用最终标量int来确定是否应执行过程。

查询有两个WITH和一个SELECT FROM第二个WITH如下所示：

WITH AAA AS (...),    
BBB AS (... FROM AAA)    
SELECT SUM(I)    
FROM BBB

查询有效 - 当它以SELECT运行时，我得到了正确的结果。

但是，当我尝试使用IF [result] = 0时，无论我在哪里放置IF条件，我都会收到语法错误。

有没有办法以我想要的方式将WITH和IF结合起来？

编辑：

WITH AAA AS 
(
    SELECT 
        OrderNo, ItemNo, QuantityOrdered, 
        SUM(QuantityDelivered) AS Delivered
    FROM 
        GGG
    WHERE 
        OrderNo = 12345
),
BBB AS 
(
    SELECT 
        OrderNo, ABS(SUM(QuantityOrdered) - SUM(Deleivered)) AS Diff
    FROM 
        AAA
)
SELECT SUM(Diff) AS Diffsum
FROM BBB
IF Diffsum <> 0 Execute...

我试过了：

DECLARE @Intdiff INTEGER;
SET @Intdiff = (
WITH ...etc

但我得到：

Msg 319，级别 15，状态 1，第 4
行 关键字"with"附近的语法不正确。如果此语句是公用表表达式、xmlnamespaces 子句或更改跟踪上下文子句，则前面的语句必须以分号结尾。

Answer 1

几乎没有答案，因为你几乎在那里，只是在错误的地方有几个位。 这应该有效吗？

--Declare a variable
DECLARE @DiffSum INT;
--Set the value based on a query
WITH AAA AS 
(
    SELECT 
        OrderNo, 
        ItemNo, 
        QuantityOrdered, 
        SUM(QuantityDelivered) AS Delivered
    FROM 
        GGG
    WHERE 
        OrderNo = 12345
),
BBB AS 
(
    SELECT 
        OrderNo, 
        ABS(SUM(QuantityOrdered) - SUM(Deleivered)) AS Diff
    FROM 
        AAA
)
SELECT @DiffSum = SUM(Diff) FROM BBB;
--Execute a stored procedure based on the result
IF @Diffsum <> 0 EXECUTE ...