Serial ID Auto Generation

/*
Script Name: Autogenerate Serial IDs
Description: This scripts are used in craeting the functionality to autogenerate your own customized serial numbers.
Author: Larry Sumuri
Date: 05.02.2010
*/

-- This table is used as a reference point to keep the record of the lated serially generated number. 
CREATE TABLE [dbo].[RefVal_SerialID](
    [SID] [int] NOT NULL,
CONSTRAINT [PK_RefVal_SerialID] PRIMARY KEY CLUSTERED 
(
    [SID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


--This procedure is contains the serial number autogeneration logic
CREATE PROCEDURE [dbo].[up_AutoGenerateSerialID]
    @ReturnValue varchar(11) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

DECLARE @CurrentSID INT
DECLARE @CurrentYear varchar(4)
DECLARE @UniqueID varchar(13)
DECLARE @Lenght INT
DECLARE @GeneratedIDValue INT
DECLARE @ConvIDValue varchar(10)

        BEGIN TRY
            BEGIN TRAN

                SET @CurrentSID = (SELECT [SID] FROM [RefVal_SerialID])
                SET @CurrentYear = (SELECT YEAR(GETDATE()))    

                IF @CurrentSID IS NULL 
                    BEGIN
                        SET @CurrentSID = 1
                    END
                ELSE
                    BEGIN
                        SET @CurrentSID = @CurrentSID + 1
                    END        
            
                SET @GeneratedIDValue = @CurrentSID
                SET @ConvIDValue = CONVERT(varchar(10), @GeneratedIDValue)

                SELECT @Lenght = Len(@GeneratedIDValue)

                IF @Lenght = 1
                    BEGIN
                        SET @UniqueID = 'SID'+@CurrentYear+'000'+@ConvIDValue
                    END
                ELSE 
                    BEGIN
                        IF @Lenght = 2
                            BEGIN
                                SET @UniqueID = 'SID'+@CurrentYear+'00'+@ConvIDValue
                            END
                        ELSE
                            BEGIN
                                IF @Lenght = 3
                                    BEGIN
                                        SET @UniqueID = 'SID'+@CurrentYear+'0'+@ConvIDValue
                                    END
                                ELSE --IF @Lenght = 4
                                    BEGIN
                                        SET @UniqueID = 'SID'+@CurrentYear+@ConvIDValue
                                    END
                            END

                    END

                INSERT INTO [RefVal_SerialID] ([SID]) VALUES (@CurrentSID)
                DELETE FROM [RefVal_SerialID] WHERE [SID] <> @CurrentSID
            
            COMMIT TRAN
            SET @ReturnValue = @UniqueID
        END TRY

        BEGIN CATCH
            ROLLBACK TRAN    
            SET @ReturnValue = 'AutogenerationOfSerialNumberFailed'
        END CATCH 
END