SQL Server: Serial number generator implementation question

Question

Hi folks:

My clients need a format of serial #: YYYYMMDD098 where YYYYMMDD represents today, 098 is a 3-digit number that will get increased by 1 every time we access the serial #.

Furthermore, the 3-digit # will trun back to zero every day.

Where can I find a reference for this solution?

Thanks.

Answer 1

For SQL Server

DECLARE @OldID char(11), @NewID char(11);
DECLARE @IDbit char(3), @CurrDate char(8)

SET @OldID = '20091027098'
--SET @OldID = '20091026098'

SELECT
    @IDbit = SUBSTRING(@OldID, 9, 3),
    @CurrDate = CONVERT(char(8), GETDATE(), 112)

IF @CurrDate <> LEFT(@OldID, 8)
    SET @NewID = @CurrDate + '000'
ELSE
    SET @NewID = @CurrDate + RIGHT('00' + CAST(CAST(@IDbit AS tinyint) + 1 AS varchar(3)), 3)

SELECT @NewID

Answer 2

That seems too much of a specific requirement for there to be an off-the-shelf solution somebody can just point you to. I'm not too clear what you mean by 'access' because you have to have an update of some kind to remember where you've got to so far. Also, I'd use more than a 3 digit counter - you're bound to overflow that some time.

I'd suggest something along these lines:

Set up a table with one row to hold the counter and set its initial value to today's date + 000 (eg. 20091027000). Create a stored procedure that, inside a transaction, increments the counter and returns the new value. To restart the sequence at 000 each morning you could either do this in a scheduled job or get the stored procedure to compare the date part of the counter with today's date and reset the counter if it's not today.

Answer 3

You will probably need a table for storing those serial numbers. Make sure you store the date and the number in separate columns, so that you can manipulate them easily. To generate a new serial number then do (in a transaction):

• Lock the table to avoid a race condition when trying to generate two serial numbers at the same time. How to do this depends on the database you are using, but you want a lock that allows reads, but disallows writes.
• SELECT max(serial_no) FROM table WHERE serial_date = ?.
• If the result is NULL, make it 0, otherwise increment it by one.
• Insert the new serial number to the table.
• Unlock the table.