On Monday I posted a T-SQL Challenge involving a custom sequence generator. You can find the puzzle details here. Thanks to all those who posted solutions.
Those who came up with the right solutions were navento, Marcello Poletti (Marc), Davide Mauri, Steve Kass, and Plamen Ratchev. Most used the following solution, which is probably the more straightforward one:
CREATE PROC dbo.InsertInvoice
@custid AS INT,
@invdate AS DATE,
@amt AS MONEY,
@invoiceid AS INT OUTPUT
AS
UPDATE dbo.Sequence
SET @invoiceid = val += 1
OUTPUT inserted.val, @custid,@invdate, @amt
INTO dbo.Invoices(invoiceid, custid, invdate, amt);
GO
This solution relies on the specialized UPDATE syntax that allows updating a column value and assigning the result value to a variable using the same statement; this solution also uses the OUTPUT INTO option that allows inserting output rows into a table.
But there’s another, quite elegant solution to this task that relies on a new feature in SQL Server 2008 called Composable DML. Here it is:
CREATE PROC dbo.InsertInvoice
@custid AS INT,
@invdate AS DATE,
@amt AS MONEY,
@invoiceid AS INT OUTPUT
AS
INSERT INTO dbo.Invoices(invoiceid, custid, invdate, amt)
SELECT val, @custid, @invdate, @amt
FROM (UPDATE dbo.Sequence
SET @invoiceid = val + 1, val += 1
OUTPUT inserted.val) AS D;
GO
Note that since a statement in T-SQL is an “all-at-once operation” at the logical level, the order of assignments that you see in the SET clause doesn’t matter. Both expressions to the right of the equal signs use the source value of the val attribute as it was before the change.
BTW, the tricky part using this approach wasn’t to increment the sequence in the Sequence table and insert the invoice row into the Invoices table in the same statement—that’s what Composable DML is all about. The tricky part was to also update the stored procedure’s output parameter with the newly generated invoice ID in the same statement. Initially I tried to use the specialized syntax for the UPDATE statement as the inner statement defining the derived table D like so:
CREATE PROC dbo.InsertInvoice
@custid AS INT,
@invdate AS DATE,
@amt AS MONEY,
@invoiceid AS INT OUTPUT
AS
INSERT INTO dbo.Invoices(invoiceid, custid, invdate, amt)
SELECT val, @custid, @invdate, @amt
FROM (UPDATE dbo.Sequence
SET @invoiceid = val += 1
OUTPUT inserted.val) AS D;
GO
But then I got the following error message:
Msg 141, Level 15, State 1, Procedure InsertInvoice, Line 0
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
Just before giving up using this approach, I realized that the solution is as simple as I showed earlier, namely to use two separate assignments—one to update the attribute and another to update the variable.
Cheers,
BG