CODEWITHSUNDEEP
sql-serversql-server-2008
Saurabh Palatkar
Saurabh Palatkar

Reputation: 3384

Generate autoincrementing varchar primary key in sql server

I want to generate varchar auto incremented primary key (order id) values for each individual order placed as shown in below format.

'O201307270001'

'O' for Order, '20130727' for date (27-jul-2013), '0001' for auto incremented value

i want to restart the auto incremented numbers (last 4 numbers in above id) start from '1' when new day starts.

Below is the examples of how I want the order ids to be generated for order placed at different time and day:

O201307270001   when date is like '2013-07-27 01:23:45.235'
O201307270002   when date is like '2013-07-27 03:12:22.212'
.
.
.
O201307270040   when date is like '2013-07-27 11:34:56.189'
.
.
//Now when new day starts:
O201307280001   when date is like '2013-07-28 00:00:00.000'
O201307280002   when date is like '2013-07-28 00:13:05.000'

please help me, how i can do it

Upvotes: 2

Views: 6304

Answers (6)

Jithin John
Jithin John

Reputation: 17

CREATE TABLE OrderNumberGenerator ( [ID] INTEGER IDENTITY(1,1), [New_ID] AS 'O'+CONVERT(varchar) ) GO

Upvotes: 0

jazzytomato
jazzytomato

Reputation: 7214

Here is what I suggest, which has the (pretty big :) ) advantage of not reading the target table and will give better performance as your table grows.

You'll have to create a short stored procedure and a parameter table.

Just call the stored procedure and it will return the right Id :

DECLARE @NewOrderId AS CHAR(13)

EXEC usp_NewOrderId @NewOrderId OUTPUT

SELECT @NewOrderId

This is what you need to create :

CREATE TABLE OrderNumberGenerator (
  Id INTEGER IDENTITY(1,1),
  CreatedDate DATE DEFAULT(GETDATE()) 
 )
GO

CREATE PROCEDURE usp_NewOrderId 
(@NewOrderIdOut char(13) OUTPUT ) 
AS 
BEGIN

   IF EXISTS (SELECT 1 FROM OrderNumberGenerator WHERE CreatedDate <> CAST(GETDATE() AS DATE) )
    TRUNCATE TABLE OrderNumberGenerator --restart the counter everyday :)

  INSERT INTO OrderNumberGenerator DEFAULT VALUES

  SELECT @NewOrderIdOut = 
  'O' +
  CONVERT(CHAR(8), GETDATE(), 112) +
  RIGHT( '000' + CAST(SCOPE_IDENTITY() AS VARCHAR(4)) ,  4 ) 

END

Check it out here http://sqlfiddle.com/#!3/fdb91/4

Upvotes: 2

Gabe
Gabe

Reputation: 86708

You can do this with an INSTEAD OF INSERT trigger, or as I would recommend, a CreateOrder stored procedure.

Here are the steps you'd have to follow:

  1. Compute the current date prefix. 'O' + CONVERT(char(8), GETDATE(), 112)
  2. Select the last order number from the table. SELECT MAX(OrderNum) FROM Order
  3. If the last order number has the current date prefix, extract the numeric suffix, increment it, and append it to the date prefix. WHEN @CurrentPrefix = LEFT(@LastOrder, 9) THEN @CurrentPrefix + RIGHT('000' + (CONVERT(int, RIGHT(@LastOrder, 4)) + 1))
  4. Otherwise, just use 0001 as the suffix. ELSE @CurrentPrefix + '0001'
  5. Use the generated key to perform the insert.

Upvotes: 0

jpw
jpw

Reputation: 44871

As the other answers already pointed out doing this might be a bad idea, but if you want to anyway this code should accomplish what you want I believe. I haven't tried all corner cases but for the small test data provided it was working.

Some test data that i tried:

declare @tab table (pk char(13))
insert @tab values ('O201307270001')
insert @tab values ('O201307270002')
insert @tab values ('O201307270003')
insert @tab values ('O201307278999')
insert @tab values ('O201307280001')
insert @tab values ('O201307280002')
insert @tab values ('O201307290001')

And then the actual code:

SELECT 
  CASE ISNULL(MAX(RIGHT(pk,4)),0) 
    WHEN 0 THEN 'O' + CONVERT(CHAR(8),GETDATE(),112) + '0001' 
   ELSE 'O' + CONVERT(CHAR(8),GETDATE(),112) + RIGHT(CAST(MAX(RIGHT(pk,4)) + 10001 AS CHAR(5)),4) END 
FROM @tab 
WHERE SUBSTRING(pk,2,8) = CONVERT(CHAR(8),GETDATE(),112)

Ideally it should be wrapped up in a function, and probably use locking too to avoid the same key being handed out twice.

Upvotes: 0

Nithesh Narayanan
Nithesh Narayanan

Reputation: 11765

Try this

DECLARE @autoInc VARCHAR(25)
SET @autoInc='O'+(SELECT convert(varchar, getdate(), 112))+
               CAST(((SELECT COUNT(*) 
                     FROM table 
                 WHERE autoColumn like 'O'+(SELECT convert(varchar, getdate(),112))+'%') 
                       +1 ) AS VARCHAR(5))

Upvotes: 0

Felipe Pereira
Felipe Pereira

Reputation: 12300

I strongly recomend you not to use this varchar generated code as the primary key of your table, use a numerical value instead and save this generated string as an order code on a separate column (It will keep you away from nightmares with future querys). You can flag this code column as "unique" and get the same result.

About how to generate the order code, if the logic behind is not as basic as an autoincrement or something like that, it would be better to have this donde on an upper layer of your system (if there is one).

Upvotes: 0

Related Questions