SQL Server Stored Procedure to generate different types of reference number

At work, there is a website that we need to generate several types of unique reference numbers. Numbers such as A000013579 for answer id and Q000024680 for question id. To generate numbers like that, we created a stored procedure. I can see that we can use this method in other websites as well.

First, we created a table to save the last used number. Let’s call it [SequenceTable]. In this table, we need 4 columns: [Type],[Prefix],[LastNumber] and [Length].
values for [Type] column could be: “Answer ID”
values for [Prefix] column could be: “A”
values for [LastNumber] column could be: 13579
values for [Length] column could be: 10

Then, we created a Stored Procedure that take value for [Type]. Look for the value of [LastNumber] where [Type] is the input. Fill 0’s using the REPLICATE function. Increase the value of [LastNumber] by 1 and update it in the table. Append prefix and return it as an output. The stored procedure is defined as follow:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_GetNextReferenceID]
  @Type as varchar(25),
  @ID varchar(20) OUTPUT
AS
DECLARE 
  @Prefix VARCHAR(10),
  @LastNumber int, 
  @Length smallint,
  @charLastNumber as varchar(15)
BEGIN
  if exists (Select * From SEQUENCETABLE Where Type = @Type)
    begin
      Select @prefix = Prefix,
          @LastNumber = LastNumber,
          @Length = Length
      From SEQUENCETABLE Where Type = @Type
      
      Select @LastNumber = @LastNumber + 1
      Update SEQUENCETABLE Set LastNumber = @LastNumber Where Type = @Type

      -- To Fill 0 in between with the REPLICATE function
      Select @charLastNumber = REPLICATE('0',@length - len(@LastNumber) - len(@prefix)) 
+ cast(@LastNumber as varchar(10))
      Select @ID = @prefix + @charLastNumber
    End
  Else
    Begin
	SELECT @ID = 'INVALID TYPE'
	RAISERROR('Invalid Type',16,1)
    End
END
Advertisements

3 comments so far

  1. My Technology Blog on

    Use SQL Server Stored Procedure to generate different types of reference number…

    At work, there is a website that we need to generate several types of unique reference numbers. Numbers such as A000013579 for answer id and Q000024680 for question id. To generate reference numbers like that, we created a stored procedure. I can see t…

  2. XRumerTest on

    Hello. And Bye.

  3. jessa on

    nothing


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: