Marcin Bator Marcin Bator - 1 month ago 8
SQL Question

How to create custom, dynamic string sequence in SQL Server

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!

Answer

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