sql - 使用 AS MERGE 的存储过程不返回任何内容？

Question

编辑:顺序发票编号是多个国家/地区的法律。

编辑:我的变量命名不当表明我想使用生成的 ID 作为键。不是这种情况。应该坚持使用“invoiceNumber”。

---

我有与此处提出的完全相同的问题 https://stackoverflow.com/a/24196374/1980516

但是，由于建议的解决方案引发语法错误，我已将其修改为使用游标。

首先，存储过程会为给定的 Business+Year 组合生成一个新的 Nr:

CREATE PROCEDURE PROC_NextInvoiceNumber @businessId INT, @year INT, @Nr NVARCHAR(MAX) OUTPUT 
AS MERGE INTO InvoiceNextNumbers ini
    USING (VALUES (@businessId, @year)) Incoming(BusinessId, Year)
        ON Incoming.BusinessId = ini.BusinessId AND Incoming.Year = ini.Year
    WHEN MATCHED THEN UPDATE SET ini.Nr = ini.Nr + 1
    WHEN NOT MATCHED BY TARGET THEN INSERT (BusinessId, Year, Nr) 
VALUES(@businessId, @year, 1)

OUTPUT INSERTED.Nr;

然后，使用该存储过程，我创建了一个 INSTEAD OF INSERT 触发器:

CREATE TRIGGER TRIG_GenerateInvoiceNumber ON Invoices INSTEAD OF INSERT
AS 
  BEGIN

    DECLARE @BusinessId INT
    DECLARE @InvoiceId INT
    DECLARE @BillingDate DATETIME2(7)

    -- Cursors are expensive, but I don't see any other way to call the stored procedure per row
    -- Mitigating factor: Mostly, we're only inserting one Invoice at a time
    DECLARE InsertCursor CURSOR FAST_FORWARD FOR
    SELECT BusinessId, Id, BillingDate FROM INSERTED

    OPEN InsertCursor
    FETCH NEXT FROM InsertCursor
    INTO @BusinessId, @InvoiceId, @BillingDate
    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @year INT
        SET @year = year(@BillingDate)

        DECLARE @Number NVARCHAR(MAX)
        EXEC PROC_NextInvoiceNumber @BusinessId, @year, @Number OUTPUT
        -- SET @Number = 'this works'

        INSERT INTO Invoices (BusinessId, Id, BillingDate, Number) 
            VALUES (@BusinessId, @InvoiceId, @BillingDate, @Number)

        FETCH NEXT FROM InsertCursor
        INTO @BusinessId, @InvoiceId, @BillingDate
    END
    CLOSE InsertCursor
    DEALLOCATE InsertCursor

    END

如果我取消注释 SET @Number = 'this works'，那么在我的数据库中，准确的字符串('this works')在 Invoice.Number 中成功设置。

不知何故，我的 OUTPUT 参数没有设置，我不明白为什么。有人可以解释一下吗？

编辑更新以回应评论(谢谢):

• 我有一个用于 Invoice 的组合键 (BusinessId, Id)。所需的最终结果是一个唯一的发票标识符 编号，形式为“20180001”，它是 businessId 中的连续数字序列。因此，企业 1 的发票编号为 20180001、20180002、20180003，而企业 2 也的发票编号为 20180001、20180002、20180003。(但复合主键不同)

• 我也不想要那个游标，但我在上面提到的问题所建议的框架内没有其他方法。

• 使用现有业务 ID 和年份手动调用 PROC_NextInvoiceNumber 返回 NULL。

• 如果我尝试在 PROC_NextInvoiceNumber 中设置 Id，我会得到 A MERGE statement must be terminated by a semi-colon (;). 如果我在 MERGE 或 中设置它无法绑定(bind)多部分标识符“INSERTED.Nr”。如果我在 MERGE 之外设置。

Answer 1

您的 OUTPUT 参数从未设置。您正在使用 OUTPUT clause of the MERGE创建结果集的语句。这与为参数赋值无关。

MERGE INTO..
USING ... ON ...
WHEN MATCHED THEN UPDATE ...
WHEN NOT MATCHED BY TARGET THEN INSERT ...
OUTPUT INSERTED.Nr;  /* <-- HERE this is the OUTPUT *clause*  */

更改代码以实际为@Nr 分配一些内容:

SET @Nr = ...

典型的方法是使用 OUTPUT 子句将所需的值存储到一个表变量中，然后将该值赋给所需的输出*变量:

DECLARE @t TABLE (Nr NVARCHAR(MAX));
MERGE INTO..
USING ... ON ...
WHEN MATCHED THEN UPDATE ...
WHEN NOT MATCHED BY TARGET THEN INSERT ...
OUTPUT INSERTED.Nr INTO @t;

SELECT @Nr = Nr FROM @t;