Serial ID Auto Generation

Use the below function to create the functionality to auto generate customized serial IDs. It can be customized even further depending on you ID generation requirements.

1. Create the table and stored procedure

2. Execute the stored procedure to autogenerate your serial ID.

The autogenerated ID can be saved again depending on what you a developing etc. etc.

/*

Function Name: Auto generate Serial IDs

Description: This function is used in creating the functionality to autogenerate your own customized serial numbers.

This table is used as a reference point to keep the record of the lated serially generated number. 

*/

CREATE TABLE AutoGenerateSerialID(

    SerialID int NOT NULL,

CONSTRAINT PK_AutoGenerateSerialID PRIMARY KEY CLUSTERED 

(SerialID ASC)) 

--This procedure is contains the serial number autogeneration logic

CREATE PROCEDURE up_AutoGenerateSerialID

    @ReturnValue varchar(11) OUTPUT

AS

BEGIN

    SET NOCOUNT ON;

DECLARE @Current_SerialID INT

DECLARE @Current_Year varchar(4)

DECLARE @UniqueID varchar(13)

DECLARE @Length INT

DECLARE @GeneratedIDValue INT

DECLARE @ConvIDValue varchar(10)

        BEGIN TRY

            BEGIN TRAN

                SET @Current_SerialID = (SELECT SerialID FROM AutoGenerateSerialID)

                SET @Current_Year = (SELECT YEAR(GETDATE()))    

                IF @Current_SerialID IS NULL 

                    BEGIN

                        SET @Current_SerialID = 1

                    END

                ELSE

                    BEGIN

                        SET @Current_SerialID = @Current_SerialID + 1

                    END        

                SET @GeneratedIDValue = @Current_SerialID

                SET @ConvIDValue = CONVERT(varchar(10), @GeneratedIDValue)

                SELECT @Length = Len(@GeneratedIDValue)

                IF @Length = 1

                    BEGIN

                        SET @UniqueID = 'SerialID'+@Current_Year+'000'+@ConvIDValue

                    END

                ELSE 

                    BEGIN

                        IF @Length = 2

                            BEGIN

                                SET @UniqueID = 'SerialID'+@Current_Year+'00'+@ConvIDValue

                            END

                        ELSE

                            BEGIN

                                IF @Length = 3

                                    BEGIN

                                        SET @UniqueID = 'SerialID'+@Current_Year+'0'+@ConvIDValue

                                    END

                                ELSE --IF @Length = 4

                                    BEGIN

                                        SET @UniqueID = 'SerialID'+@Current_Year+@ConvIDValue

                                    END

                            END

                    END

                INSERT INTO AutoGenerateSerialID (SerialID) VALUES (@Current_SerialID)

                DELETE FROM AutoGenerateSerialID WHERE SerialID <> @Current_SerialID

            COMMIT TRAN

            SET @ReturnValue = @UniqueID

        END TRY

        BEGIN CATCH

            ROLLBACK TRAN    

            SET @ReturnValue = 'AutogenerationOfSerialNumberFailed'

        END CATCH 

END