How to create custom, dynamic string sequence in SQL Server

Question

Is there any way to dynamically build sequences containing dates/strings/numbers in SQL Server?

In my application, I want every order to have a unique identificator that is a sequence of: Type of order, Year, Month, Incrementing number

(ex: NO/2016/10/001, NO/2016/10/002)

where NO = "Normal order", 2016 is a year, 10 is a month and 001 is an incrementing number. The idea is that it is easier for employees to comunicate using these types of identificators (of course this sequence would not be primary key of database table)

I know that I could create a stored procedure that would take Order type as an argument and return the sequence, but I'm curious if there is any better way to do it.

Cheers!

Just curious. Max 999 orders per month or year or until the end-of-time? — John Cappelletti
– John Cappelletti, Commented Oct 23, 2016 at 19:58
@JohnCappelletti Per month but it's just an example. I wouldn't limit it to 999 per month in the real application of course. — Marcin Bator
– Marcin Bator, Commented Oct 23, 2016 at 20:25

Gottfried Lesigang · Accepted Answer · 2016-10-23 21:32:21Z

1

An IDENTITY column might have gaps. Just imagine an insert which is rollbacked out of any reason...

You could use ROW_NUMBER() OVER(PARTITION BY CONVERT(VARCHAR(6),OrderDate,112) ORDER BY OrderDate) in order to start a sorted numbering starting with 1 for each month. What will be best is depending on the following question: Are there parallel insert operations?

As this order name should be unique, you might run into unique-key-violations where you'd need complex mechanisms to work around...

If it is possible for you to use the existing ID you might use a scalar function together with a computed column (might be declared persistant):

CREATE TABLE OrderType(ID INT,Name VARCHAR(100),Abbr VARCHAR(2));
INSERT INTO OrderType VALUES(1,'Normal Order','NO')
                           ,(2,'Special Order','SO');
GO
CREATE FUNCTION dbo.OrderCaption(@OrderTypeID INT,@OrderDate DATETIME,@OrderID INT)
RETURNS VARCHAR(100)
AS
BEGIN
    RETURN ISNULL((SELECT Abbr FROM OrderType WHERE ID=@OrderTypeID),'#NA')
         + '/' + CAST(YEAR(@OrderDate) AS VARCHAR(4))
         + '/' + REPLACE(STR(MONTH(@OrderDate),2),' ','0')
         + '/' + REPLACE(STR(@OrderID,5),' ','0')
END
GO


CREATE TABLE YourOrder
(
     ID INT IDENTITY
    ,OrderDate DATETIME DEFAULT(GETDATE())
    ,OrderTypeID INT NOT NULL --foreign key...
    ,Caption AS dbo.OrderCaption(OrderTypeID,OrderDate,ID)
);
GO

INSERT INTO YourOrder(OrderDate,OrderTypeID)
VALUES({ts'2016-01-01 23:23:00'},1)
     ,({ts'2016-02-02 12:12:00'},2)
     ,(GETDATE(),1);
GO

SELECT * FROM YourOrder

The result

ID  OrderDate               OrderTypeID     Caption
1   2016-01-01 23:23:00.000      1          NO/2016/01/00001
2   2016-02-02 12:12:00.000      2          SO/2016/02/00002
3   2016-10-23 23:16:23.990      1          NO/2016/10/00003

edited Oct 23, 2016 at 21:32

answered Oct 23, 2016 at 21:17

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Marcin Bator Over a year ago

Thank you for the answer. It's the cleanest and most logical solution so far. The only problem would still be resetting the number at the end of caption, but I will resolve it by storing last value in a service table in the database and running a job at the last day of each month and resetting it back to 0.

Gottfried Lesigang Over a year ago

@MarcinBator, starting with SQL Server 2012 there is SEQUENCE - find details here.

Gottfried Lesigang Over a year ago

@MarcinBator And read about restarting a sequence here

Marcin Bator Over a year ago

Using a sequence instead of service table row and restarting it using a job would be a pretty sweet idea indeed. Thanks again!

Mat Richardson · Accepted Answer · 2016-10-23 19:53:51Z

1

You could create a computed column in your table definition which concatenates other values in your database into the kind of Identifier you're looking for.

Try this for a simplified example:-

CREATE TABLE Things ( 
  [Type of Order] varchar(10), 
  [Year] int,
  [Month] int,
  [Inc Number] int identity(1,1),
  [Identifier] as [Type of Order] + '/' + cast([Year] as varchar) + '/' + cast([Month] as varchar) + '/' + cast([Inc Number] as varchar)
)

insert into Things
values
('NO',2016,10)

select * from Things

If you wanted to do something more complex you could always use a trigger to update the column post insert or update.

answered Oct 23, 2016 at 19:53

Mat Richardson

3,5985 gold badges37 silver badges57 bronze badges

5 Comments

TT. Over a year ago

Note that an IDENTITY does not guarantee that there won't be gaps in the created identifiers (eg, 1, 2, 4, 7, 8, 9 is entirely possible). Perhaps that is not what the OP wants.

Marcin Bator Over a year ago

@TT. That's true, but since in my application an order can never be deleted, it wouldn't be a big problem.

Marcin Bator Over a year ago

It's one way to do it, but I am storing types of orders in a different table (and table Order has field "Type" that references OrderTypes(Id)). I think I will have to write a stored procedure and store last order number in a settings table and have to find a way to reset the number when the month ends.

TT. Over a year ago

@MarcinBator Even then you have absolutely no guarantee at all that there won't be gaps.

Marcin Bator Over a year ago

@TT. Thanks for that comment, I did not know that.

Collectives™ on Stack Overflow

How to create custom, dynamic string sequence in SQL Server

2 Answers 2

4 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related