Generating Reference Number -

Hi All,

I would like to know the stored procedure to create reference number in a table with fixed length.
for example "XYZ16030001" where xyz - is prefix ,16- year ,03- month, 0001 - number starting from 0001 - 9999. When 1604 arrives following number has to restart from first 00001, when next year 17 comes it has to start from XYZ17010001

CREATE TABLE [dbo].[Chaa](
[ChaId] [int] IDENTITY(1,1) NOT NULL,
[ChaName] varchar NOT NULL,
[PortId] [int] NOT NULL,
[StateId] [int] NULL,
[CountryId] [int] NOT NULL,
[Address] varchar NOT NULL,
[PhoneNumber] [int] NOT NULL,
[EmailId1] varchar NOT NULL,
[CreatedDate] [date] NOT NULL,
[CreatedById] [int] NULL,
[ModifiedDate] [date] NULL,
[ModifiedById] [int] NULL,
[IsActive] [bit] NOT NULL,
[ChaReferenceId] [varchar] (50) NULL



CHA - Table Name,
1603 - Year & Month,
0001 - 9999 - Sequence number generation so it should be like this -

Not exactly sure I understand what you're looking for, but this might get you started:

declare @prefix char(3)='CHA';

with chaa(chareferenceid)
  as (          select 'CHA16030001'
      union all select 'CHA16039099'
      union all select 'CHA16040001'
      union all select 'CHA16040891'
      union all select 'CHA16050001'
select left(max(chareferenceid),7)
      +right(10000+cast(right(max(chareferenceid),4) as int)+1,4) as chareferenceid
  from (select @prefix
              +'0000' as chareferenceid
        union all
        select max(chareferenceid) as chareferenceid
          from /*dbo.*/chaa
         where chareferenceid like @prefix
       ) as a

1 Like

CHA16030001 - is the format in which the chareferenceid should be generated !

CHA - Prefix
03- month
the number generation should be from 0001

Use identity to generate most of it, with a computed column to add the 'CHA'.

CREATE TABLE [dbo].[Chaa](
[ChaId] [int] IDENTITY(16030001,1) NOT NULL,
[ChaName] AS CAST('CHA' + CAST(ChaId AS char(8)) AS varchar(50)),

Then, schedule a job on the first of the month at midnight to reseed the identity for the upcoming month:
DECLARE @identity_seed int
SET @identity_seed = SUBSTRING(CONVERT(char(8), GETDATE(), 112), 3, 4) * 10000
--SELECT @identity_seed
DBCC CHECKIDENT('dbo.Chaa', RESEED, @identity_seed)

You should look at using a sequence - may be a better option: