Stored procedure with AS MERGE not returning anything?

Question

EDIT: Sequential invoice numbering is the law in multiple countries.

EDIT: Poor variable naming on my part suggested I wanted to use my generated Id as a key. This is not the case. Should have stuck with 'invoiceNumber'.

---

I have the exact same question as posed here https://stackoverflow.com/a/24196374/1980516

However, since the proposed solution threw a syntax error, I've adapted it to use a cursor.

First, there is the stored procedure that generates a new Nr, for a given Business+Year combination:

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;

Then, using that stored procedure, I've created an INSTEAD OF INSERT trigger:

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

If I uncomment SET @Number = 'this works', then in my database that exact string ('this works') is successfully set in Invoice.Number.

Somehow, my OUTPUT parameter is not set and I can't figure out why not.. Can someone shed a light on this?

EDIT update in response to comments (thank you):

• I have a composite key (BusinessId, Id) for Invoice. The desired end result is a unique Invoice Identifier Number of the form '20180001' that is a continuous sequence of numbers within the businessId. So business 1 has invoice Numbers 20180001, 20180002, 20180003 and business 2 also has invoice numbers 20180001, 20180002, 20180003. (But different composite primary keys)

• I don't want that cursor either, but I saw no other way within the framework as suggested by the question I refer to up above.

• Manual call of PROC_NextInvoiceNumber with existing business id and year returns NULL.

• If I try to set Id in PROC_NextInvoiceNumber, I get A MERGE statement must be terminated by a semi-colon (;). if I set it inside the MERGE or The multi-part identifier "INSERTED.Nr" could not be bound. if I set outside the MERGE.

Answer 1

Your OUTPUT parameter is never set. You are using the OUTPUT clause of the MERGE statement to create a result set. This is unrelated to assigning a value to a parameter.

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

Change the code to actually assign something to @Nr:

SET @Nr = ...

The typical way is to use the OUTPUT clause to store the desired value into a table variable and then assign the value to the desired output *variable:

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;