Generating Reference Number -

Transact-SQL
1

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
) ON [PRIMARY]

ChaReferenceId-

CHA16030001,
CHA16039099,
CHA16040001,
CHA16040891,
CHA16050001,

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

2

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
              +right(year(current_timestamp)*100
                    +month(current_timestamp)
                    ,4
                    )
              +'0000' as chareferenceid
        union all
        select max(chareferenceid) as chareferenceid
          from /*dbo.*/chaa
         where chareferenceid like @prefix
                                   +right(year(current_timestamp)*100
                                         +month(current_timestamp)
                                         ,4
                                         )
                                   +'%'
       ) as a
;
1 Like
3

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

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

4

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)

5

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


https://msdn.microsoft.com/en-us/library/ff878091.aspx
https://msdn.microsoft.com/en-us/library/ff878058(v=sql.110).aspx