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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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