Sequential Numbers for Business Apps

• Download GenerateSN.zip - 287.2 KB

Introduction

In this modern life everything need to have identifying number, starting from your personal ID, you air flight number, the grocery invoice number , and the MAC number of the device you use to read this article. In any financial document the second most important number behind the amount is the document reference id or in other words :serial number.

Generating this IDs, Serial Numbers, Reference Numbers , or whatever you will call them is not an easy job. because generating this numbers is depend on many varieties like:
it is Digits only or Digits & Letters?
every organization structure this number in a way that best suits their needs , but this structure may change in future.
sometimes need leading zero support
sometimes should had expiry date.
it should be unforgettable or easy to read and pass through phone as example (it not like generating a random complex password for a new registered user because user will copy-paste it from his mail and change it to more human phrase).
it may include variables like fiscal year, branch code, ..
Not easily traceable ,so people cannot know how many invoices you sell since establishing your firm (and this is why some stores start from invoice 1000 or 5000 or .. )
take into consideration the need to has different schemas in the same system (customers payments, supplier payments, invoices, purchase orders, ..).

and many other points.

What I present here is not the ultimate solution because each project or company has its special needs but you can customize it easily to meet your requirements.

Tell me how it works?

The Design

below the design diagram it contain three main parts:

The Serial Number Schema Table: Named “SerialSchemas”, Here we save the serial number configurations as below:

• Initial Value: The initial value we want to start with, in some cases we do not want to start with because we replace older existing system or business owner wants to start with 1000 instead of 0 to give better impression to clients.
• Increment: increment value for the next serial number.
• Serial Mask: here we define the serial number template depending on predefined tags.

1. {#} : The numeric value of the generated number.
2. {yy}, {yyyy} : both of them represent the year in ‘yy’ and ‘yyyy’ formats.
3. {mm} : the current month.
4. {dd} : the current day.
5. {fy} : the fiscal year assigned to schema.
6. {co} : the company or branch assigned to schema.

• Leading Zeros: in some cases its required to maintain the style of the serial number specially on printed invoices, the tool will use leading zero value to know the length of the serial number and fill zeros in the empty spaces (as example for value 29 with leading zero 5 it becomes 00029).
• Expiry Date: in some cases you need to keep schema valid for a limited time (like invoices for 2014 only), for unlimited time keep this field empty.
• Max Value: Specifies the maximum value for or the sequence number, If NULL it will be the maximum value of bigint data type 2^63-1 (9,223,372,036,854,775,807)
• Min Value: Specifies the minimum value for the sequence number, If NULL it will be the minumem valueof bigint data type -2^63 (-9,223,372,036,854,775,808)
• Cycle: specifies whether the sequence should restart from the minimum value (or maximum for descending sequence) or throw an exception when its minimum , maximum value is exceeded or schema is expired. The default cycle option for new sequence is 0 = No Cycle.
  Note that cycling restarts from the Initial Value.
• Last Serial: this a read only value used by the stored procedure to know the last number used and add the increment value to use it in the new generated number.

Sequence Type Table: Named “SequenceTypes”, this table is used as reference to categorize and describe the schemas, it is up to you, you can replace this table by adding Schema name column to the schemas table or change the uniqueidentifier reference to byte value you can assign it with enumeration into your application (which will make it easier to distinguish schemas) , anyway in the current time the sequence type is used as reference to retrieve the schema.

Lookup Tables: Companies and FiscalYears tables, you can define lookups tables as you like and add reference fields in the schemas table and define a tag for them in the stored procedure in our sample {co} represent the company tag, {fy} represent fiscal year, adding those tags to the serial mask will add their values to the serial number when its generated.

Serial Number Shelf: Named "SerialNumbersShelf", In some senarios you need to generate sequense number with no guarantee that it will be used, as example user open new invoice form and you show this new invoice number in the form then the user choose to close the form without saving, in this case a gap of sequences will appear, to avoid that every generated sequence will be saved temporarily into the shelf table where its "Hold" flag will set to true , which mean the sequence is currently reserved if the user choose to discard invoice saving then you should release the number by set its "Hold" flag to be false, If user choose to use the number by saving the invoice you should delete it from the shelf.

The Code

below the stored procedure which responsible for generating the serial number

Create procedure [dbo].[uspGenerateSerialNumber]
     @SequenceType uniqueidentifier 
    ,@SerialValue bigint output
    ,@SerialNumber nvarchar(100)  output
as

BEGIN  

DECLARE @TranName VARCHAR(20)
SELECT @TranName = 'GenerateSerialNumber'

BEGIN TRANSACTION @TranName

BEGIN TRY


IF  Not Exists(Select 1 From SerialSchemas tblSerialSchema
Where 
(tblSerialSchema.Sequence_Type_Id = @SequenceType)
)
Begin
    RAISERROR ('Serial Number Schema not exists!', 16, 1)
End

/*  Constants   */
Declare @SerialValueTag varchar(3) = '{#}'
Declare @YearTag varchar(4) = '{yy}'
Declare @Year2Tag varchar(6) = '{yyyy}'
Declare @MonthTag varchar(4) = '{mm}'
Declare @DayTag varchar(4) = '{dd}'
Declare @FiscalYearTag varchar(4) = '{fy}'
Declare @CompanyTag varchar(4) = '{co}'

/*  Variables   */
Declare @SchemaId uniqueidentifier 
Declare @IncrementValue int 
Declare @InitialValue int 
Declare @FiscalYearId uniqueidentifier
Declare @CompanyId uniqueidentifier
Declare @LeadingZerosLength nvarchar(50) 
Declare @ExpiryDate datetime2
Declare @MaxValue bigint
Declare @IsCycle bit
Declare @LastSerial bigint

Select 
         @SchemaId = tblSerialSchema.Id
       , @IncrementValue = ISNULL(NULLIF(tblSerialSchema.Increment, 0), 1)
       , @InitialValue = ISNULL(tblSerialSchema.Initial_Value, 0)
       , @SerialNumber = ISNULL(tblSerialSchema.Serial_Mask, @SerialValueTag) 
       , @FiscalYearId = tblSerialSchema.Fiscal_Year_Id
       , @CompanyId = tblSerialSchema.Company_Id
       , @LeadingZerosLength = ISNULL(Leading_Zeros_Length, 0)
       , @ExpiryDate = Expiry_Date
       , @MaxValue = Max_Value
       , @LastSerial = Last_Serial
       , @IsCycle = ISNULL(Is_Cycle, 0)
From SerialSchemas tblSerialSchema
Where 
( tblSerialSchema.Sequence_Type_Id = @SequenceType )

IF (@ExpiryDate is NOT NULL)
    Begin
        IF (DATEDIFF(MINUTE, GETDATE(), @ExpiryDate) <= 0 )
            Begin
                RAISERROR ('Schema is expired!', 16, 1)
            End
    End

SELECT @SerialValue = ISNULL(MIN(Serial_Value), IsNull(@LastSerial, @InitialValue - @IncrementValue ) + @IncrementValue) FROM SerialNumbersShelf WHERE Is_Hold = 0 AND Sequence_Type_Id = @SequenceType

IF (@MaxValue is NOT NULL)
    Begin
        IF (@SerialValue > @MaxValue AND @IsCycle = 1)
            Begin
                SET @SerialValue = @InitialValue
            End
            ELSE
            Begin
                RAISERROR ('Max value exceeded', 16, 1)
            End
    End

IF Not Exists(SELECT 1 FROM SerialNumbersShelf WHERE Sequence_Type_Id = @SequenceType AND Serial_Value = @SerialValue)
BEGIN
    Insert into SerialNumbersShelf 
    (Sequence_Type_Id, Serial_Value, Is_Hold) 
    values 
    (@SequenceType, @SerialValue , 1)
END
ELSE
BEGIN
    UPDATE SerialNumbersShelf
    SET Is_Hold = 1
    WHERE 
    Sequence_Type_Id = @SequenceType AND Serial_Value = @SerialValue
END



If (LEN(@SerialValue) > @LeadingZerosLength)
 BEGIN
    Set @LeadingZerosLength = LEN(@SerialValue)
 END

/* Serial */
IF (PATINDEX('%'+@SerialValueTag+'%', @SerialNumber) > 0)
 Begin
    Set @SerialNumber = REPLACE(@SerialNumber,@SerialValueTag,RIGHT(REPLICATE('0', @LeadingZerosLength) + CONVERT(NVARCHAR,@SerialValue),@LeadingZerosLength))
 End
/* Year */
IF (PATINDEX('%'+@YearTag+'%', @SerialNumber) > 0)
    Begin
     Set @SerialNumber = REPLACE(@SerialNumber,@YearTag,RIGHT(YEAR(GetDate()), 2))
    End
IF (PATINDEX('%'+@Year2Tag+'%', @SerialNumber) > 0)
    Begin
        Set @SerialNumber = REPLACE(@SerialNumber,@Year2Tag,YEAR(GetDate()))
    End
/* Month */
IF (PATINDEX('%'+@MonthTag+'%', @SerialNumber) > 0)
    Begin
     Set @SerialNumber = REPLACE(@SerialNumber,@MonthTag,Month(GetDate()))
    End
/* Day */
IF (PATINDEX('%'+@DayTag+'%', @SerialNumber) > 0)
    Begin
     Set @SerialNumber = REPLACE(@SerialNumber,@DayTag,Day(GetDate()))
    END
/* Fiscal Year */
IF (PATINDEX('%'+@FiscalYearTag+'%', @SerialNumber) > 0)
Begin
    Declare @FiscalYear nvarchar(10) = ''
    Select @FiscalYear =  Code  From FiscalYears where (@FiscalYearId IS NOT NULL AND Id = @FiscalYearId)
    Set @SerialNumber = REPLACE(@SerialNumber,@FiscalYearTag, @FiscalYear)
End
/* Compamy */
IF (PATINDEX('%'+@CompanyTag+'%', @SerialNumber) > 0)
Begin
    Declare @CompanyName nvarchar(10) = ''
    Select @CompanyName =  Code  From Companies where (@CompanyId IS NOT NULL AND Id = @CompanyId)
    Set @SerialNumber = REPLACE(@SerialNumber,@CompanyTag, @CompanyName)
End

UPDATE SerialSchemas
   SET Last_Serial = @SerialValue
Where
Id = @SchemaId

Select @SerialNumber SerialNumber, @SerialValue SerialValue

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION @TranName

DECLARE @ErrorMessage NVARCHAR(4000);
SELECT @ErrorMessage = 'Cannot Generate Serial Number, ' + ERROR_MESSAGE()
RAISERROR (@ErrorMessage, 16, 1)

END CATCH

     IF @@TRANCOUNT > 0
         COMMIT TRANSACTION @TranName

END

First it check if the serial number schema for the passed sequence type exist , then it defined some constant values represent tags values. After that it retrieve the schema data and hold it in predefined variables.

next step is check the validity of schema using @ExpiryDate variable , if schema expired error will be raised.

if Schema is not expired the procedure check if there is serial values avilable in the shelf except that it will calculate the new serial number value by adding Increment Value to Last Serial value.

If the max value is not null and the serial value exceeded it the procedure will check cycle flag value if it is true it will reset the serial value to intial value. then it start replacing the tags in the serial number mask with its actual values. and congratulation new serial number is ready

Just call uspGenerateSerialNumber stored procedure , you can call it directly in your application to generate the serial number on the fly or call it from another stored procedure in your database.
uspGenerateSerialNumber has three parameters :
@SequenceType: input parameter used as key to access schema row.
@SerialValue: output integer parameter represent the numeric value of generated serial.
@SerialNumber: output string parameter represent the serial number.

let’s say you have invoices table, the structure of the table will be like below:

you will use the serial number field to store the invoice serial number (which is formatted regarding the serial mask) which will appear in the invoice form and report, in the same time you will keep the plan number value of the serial , this will help you in future to update the serial number if the format of serial number changed , as example your serial mask is Inv-{#}
the serial number will look like Inv-001 and serial value will be 1 if the customer wanted the serial mask to be Inv-{#}-{yy} simply you will update the serial number by applying the new mask on the stored serial value.

With example life is better

I wrote a simple example to help you to test and understand how its work
to run it open your SQL Server Management Studio and create a new database, call it “SerialNumberGenerator” then run the script.sql file to create tables and stored procedure.
Open the project in visual studio and set the connection string in project settings to meet your station settings.
Run the application and have fun

The sky's the limit

Do not focus on "invoice number" generation the presented code/mecanism colud be applied on anything needs number (read the article intro), this could be applied to generate queuing system ticket number, Cheque MICR number, trip number, emplyoyee number, car plate number ....

SQL Server Sequence Numbers

If you are looking for a "Native" solution to generate sequence numbers
SQL Server 2012 inteduced a "sequence" which is a "user-defined schema-bound object that generates a sequence".
Using "CREATE SEQUENCE" statement you can generate sequence numbers and specifies its properties, you can define the initial value, increment/decrement value, mine value, max value, enable cycling, and caching.

Anyway SQL Server sequence numbers lacks the flexbilty and extensibility to meet all business needs you can use it for basic numbering needs for more advanced options click on download link at the top of this article.

Visit the following link for more details about SQL Server Sequence Numbers:
http://msdn.microsoft.com/en-us/library/ff878058(v=sql.110).aspx

Points of Interest

Manpulating in database or in code does not matter, I present the mecanism and you should choose what is convenient and meet your project requierments.

If you have any idea or improvement please mention it the comments below and I will try to include them in any future update.

If you liked this article and found it usefull please award it with some stars.

History

2014-11-29 First Release Version 1

2014-12-05 Version 2

• Update: Use Transaction
• Update: script to support older versions of SQL Server
• Added: serial numbers shelf.
• Added: reserved serial numbers.
• Added: MaxValue, and Cycle options
• Present a brief description about SQL Server Sequence Numbers