T-SQL Puzzle – Custom Sequence

There are certain cases where you need a sequence generator and the IDENTITY property is not an option. One such case is when your sequence must guarantee no gaps (e.g., with systems that do not allow gaps in invoice IDs). The IDENTITY property doesn’t guarantee no gaps since an increment of the identity value isn’t undone when the INSERT that caused it to increment fails. There may be other reasons why you would want to use an alternative sequence generator instead of the IDENTITY property. The T-SQL Puzzle involves creating such a custom sequence generator in SQL Server 2008.

Run the following code to create the Invoices and Sequence tables:

SET NOCOUNT ON;

USE tempdb;

IF OBJECT_ID('dbo.Invoices', 'U') IS NOT NULL

  DROP TABLE dbo.Invoices;

GO

CREATE TABLE dbo.Invoices

(

  invoiceid INT NOT NULL,

  custid    INT NOT NULL,

  invdate   DATE NOT NULL,

  amt       MONEY NOT NULL,

  CONSTRAINT PK_Invoices PRIMARY KEY(invoiceid)

);

IF OBJECT_ID('dbo.Sequence', 'U') IS NOT NULL

  DROP TABLE dbo.Sequence;

GO

CREATE TABLE dbo.Sequence(val INT NOT NULL);

INSERT INTO dbo.Sequence(val) VALUES(0);

The Invoices table holds invoice information, and the Sequence table holds the last used invoice ID. Your task is to write a stored procedure that accepts details of a new invoice as input, increments the sequence value in the Sequence table by 1, and uses the new value along with the invoice info provided as input to insert a new row into the Invoices table. The procedure should return the newly generated invoice ID as an output parameter. Here’s the header of the stored procedure:

CREATE PROC dbo.InsertInvoice

  @custid    AS INT,

  @invdate   AS DATE,

  @amt       AS MONEY,

  @invoiceid AS INT OUTPUT

AS

<your statement goes here>;

The challenge: do it all using a single statement! That is, the stored procedure’s body must be made of a single statement that does three things:

1.    Increments the sequence value in the Sequence table

2.    Inserts the new invoice row (with the newly generated invoice ID) into the Invoices table

3.    Assigns the new sequence value to the output parameter @invoiceid

You can use the following code to test your procedure:

DECLARE @newinvid AS INT;

EXEC dbo.InsertInvoice

  @custid    = 1,

  @invdate   = '20090212',

  @amt       = 1000.00,

  @invoiceid = @newinvid OUTPUT;

SELECT @newinvid;

I’ll post the solution in a new entry this Friday.

Good luck!

BG