How can Make an Generating auto increment unique ID which contain today date, month, year and some number in SQL Server

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

, +

I want to create a auto generate unique Id which contains today's date, month, year and some number.

Ex. in year 2016-only 16 will be used,
for the month of January 01 will be used,
for today's day 26 would be used,
and the slno is starts 0001 to 9999

So my id should be like 1601250001.

The date changes everyday and number should be increases ..

I Have tried ..

SQL

Create procedure InsertRegistration
(
    @name nvarchar(50),
    @Address nvarchar(100)
)
as
begin
    DECLARE @BdID VARCHAR(25)

    SET @BdID = (SELECT convert(varchar, getdate(), 112))+
                 CAST(((SELECT COUNT(*) 
                     FROM BasicInformation  
                 WHERE @BdID like (SELECT convert(varchar, getdate(),112))+'%') 
                       +1 ) AS VARCHAR(5))

     Insert into BasicInformation(BdID, name, address) 
     values (@BdID, @name, @address)
end

It's working but the increment is not happening for each registration. I am a beginner. Please help me

Posted 25-Jan-16 19:47pm

Member 12287325

Updated 25-Jan-16 20:14pm

v2

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Answer 1 · 2016-01-25T20:10:00

A moderate level design could be like

* Have a table COUNTERS(ID, currentValue, maskingRules)
* With values (1, 1, 'yymmdd9999')
* Have a SQL Function which does following
* Read masking rule
* Parse it and fill it accordingly
* Update CurrentValue
* Return the accumulated value to the business layer

This will help in following ways
* You have an independent function that can be easily tested
* You can define different masking rules
* You can change the rules instantly (on production)

This approach do have side effects, significant one is the COUNTERS table could it self be a bottleneck on load because of locks if too many counters have been defined

How can Make an Generating auto increment unique ID which contain today date, month, year and some number in SQL Server

1 solution

Solution 1

Add your solution here

Preview 0

How can Make an Generating auto increment unique ID which contain today date, month, year and some number in SQL Server

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us