CODEWITHSUNDEEP
sqlsql-server
Marcin Bator
Marcin Bator

Reputation: 381

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!

Upvotes: 0

Views: 3605

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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

Upvotes: 1

Mat Richardson
Mat Richardson

Reputation: 3606

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.

Upvotes: 1

Related Questions