SQL Server: auto-generated custom format sequence number

Question

I am working with Microsoft SQL Server 2014. In our requirement, custom formatted sequence number is include.

The sequence number format is CAT-YYYY-MM-NNNNNN. Sample data:

CAT-2016-10-000001
CAT-2016-10-000002
                 .
                 .
                 .
CAT-2016-10-999999

I don't want to use GUID or any other and I want to work with a procedure or function.

So, I am trying with this:

CREATE TABLE [category]
(
    [id] int NOT NULL UNIQUE IDENTITY,
    [category_no] nvarchar(20) NOT NULL,
    [category_name] nvarchar(50) NOT NULL,
    PRIMARY KEY ([id])
);

CREATE FUNCTION generate_category_no() 
RETURNS CHAR(20) 
AS 
BEGIN 
    DECLARE @category_no CHAR(20) 
    SET @category_no = (SELECT MAX(category_no) FROM category) 

    IF @category_no IS NULL
         SET @category_no = 'CAT-' + YEAR(getDate()) + '-' + MONTH(getDate()) + '-000001' 

    DECLARE @no int 
    SET @no = RIGHT(@category_no,6) + 1 

    RETURN 'CAT-' + YEAR(getDate()) + '-' + MONTH(getDate()) + '-' + right('00000' + CONVERT(VARCHAR(10),@no),6) 
END
GO

ALTER TABLE category DROP COLUMN category_no;
ALTER TABLE category ADD category_no AS dbo.generate_category_no();

INSERT INTO category (category_name) 
VALUES ('BMW'), ('JAGUAR');

When I run the above SQL in step-by-step, it is OK. It shown no error. But when I run the following command:

SELECT * FROM category;

it shows the following error:

Msg 217, Level 16, State 1, Line 1
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

I don't know how to solve this one. And even I don't know my function has worked or not. I referenced from internet for this function.

ADDED

I need to reset sequence no for every month. Eg. for next month, no should be as follow:

CAT-2016-11-000001

Please, enlighten me. Thanks in advance!

What if two sessions run (SELECT MAX(category_no) FROM category) at the same time? Who gets the +1 value? — Alex K.
– Alex K., Commented Oct 21, 2016 at 11:41
You are getting error on selecting data from table or while executing the function or while creating the function ? Are there any triggers associated with table ? — Aditya
– Aditya, Commented Oct 21, 2016 at 11:47
There is infinity loop happens here, so the SQL Server engine is preventing this via setting the maxamum of nesting level equals 32. The infinity loop is happening via calling select * from category that call your function that select from the same table .. so on. if you select * from category where 1= 2. there is no error, and there is no data too :) , but when selecting data, the infinity loop is happening. — ahmed abdelqader
– ahmed abdelqader, Commented Oct 21, 2016 at 12:12
No, when I run the sql Select id, category_name, category_no From category Where id < 3;, it shown the same error. — R.Katnaan
– R.Katnaan, Commented Oct 24, 2016 at 3:41

User · Accepted Answer · 2016-10-21 11:55:39Z

3

Modify your function as below

ALTER TABLE category DROP COLUMN category_no;

alter FUNCTION dbo.generate_category_no( @id int) 
RETURNS CHAR(20) 
AS 
BEGIN 

    RETURN 'CAT-' + cast(YEAR(getDate()) as varchar(10)) + '-' + cast(MONTH(getDate()) as varchar(10))+ '-' + right('00000' + CONVERT(VARCHAR(10),@id),6) 
END



ALTER TABLE category ADD category_no AS dbo.generate_category_no(id);

INSERT INTO category
(category_name) 
VALUES 
('BMW13'),
('JAGUAR');

SELECT * FROM category will give the below result.

1   BMW     CAT-2016-10-000001  
2   JAGUAR  CAT-2016-10-000002  
3   BMW1    CAT-2016-10-000003  
4   BMW13   CAT-2016-10-000004

answered Oct 21, 2016 at 11:55

User

8048 silver badges22 bronze badges

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

2 Comments

ahmed abdelqader Over a year ago

Good solution , just Note: you linked the category_no with id, so if there is a gap within identity , the category_no also has the same scope of gap. is it an issue on asker business or not ?

User Over a year ago

Then try below query select id,category_name,'CAT-2016-10-00000'+cast(row_number() over(order by id) as varchar(10)) as catid from category

Joe Taras · Accepted Answer · 2016-10-21 11:42:54Z

1

Try this:

To initialize your new field:

ALTER TABLE category DROP COLUMN category_no;
ALTER TABLE category ADD category_no CHAR(20)

UPDATE category set category_no = dbo.generate_category_no()

For other insert:

CREATE TRIGGER [dbo].[category_i] 
ON  [dbo].[category]
AFTER INSERT
AS BEGIN
    UPDATE category
    SET category_no = dbo.generate_category_no()
    FROM inserted
    WHERE category.pk = inserted.pk
END

But you can try to use SEQUENCE feature, available on Sql Server by 2012 version

About SEQUENCE you can see here

answered Oct 21, 2016 at 11:42

Joe Taras

15.4k7 gold badges45 silver badges59 bronze badges

2 Comments

R.Katnaan Over a year ago

I just upvote your answer, because trigger is not my aim. And your answer is right in one way. But, I just want to use function in column. Now, I got it. Thanks.

Joe Taras Over a year ago

@NaingWinHtun: Hi dear, thank you for your upvote. Obviously you accept the answer accomplish your aim, so it's right yo have accepted yours ;) Have a nice day

Pரதீப் · Accepted Answer · 2016-10-21 11:49:09Z

1

Biggest flaw in your function is it will not work for Batch Insert's

Since you have ID auto generated, here is a easier way to do this

category_no AS Concat('CAT-', Year(Getdate()), '-', Month(Getdate()), '-', RIGHT(Concat('00000', id), 6))

Demo

CREATE TABLE #seq
  (
     id   INT IDENTITY(1, 1),
     name VARCHAR(10),
     category_no AS Concat('CAT-', Year(Getdate()), '-', Month(Getdate()), '-', RIGHT(Concat('00000', id), 6))
  )

INSERT INTO #seq
            (name)
VALUES      ('val')

Result :

id  name    category_no
--  ----    -----------
1   val     CAT-2016-10-000001

edited Oct 21, 2016 at 11:49

answered Oct 21, 2016 at 11:42

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Comments

R.Katnaan · Accepted Answer · 2016-10-24 10:21:53Z

1

Finally, I solved the problem. My Function look like as follow:

CREATE FUNCTION generate_category_no() 
RETURNS CHAR(20) 
AS 
BEGIN 
    DECLARE @category_no CHAR(20) 
    SET @category_no = (SELECT MAX(category_no) FROM category WHERE category_no LIKE CONCAT('CAT-', YEAR(getDate()), '-', MONTH(getDate()), '-%')) 
    IF @category_no is null SET @category_no = CONCAT('CAT-', YEAR(getDate()), '-', MONTH(getDate()), '-000000')
    DECLARE @no INT 
    SET @no = RIGHT(@category_no,6) + 1 
    RETURN CONCAT('CAT-', YEAR(getDate()), '-', MONTH(getDate()), '-', RIGHT('00000' + CONVERT(VARCHAR(10),@no),6))
END
GO

And I insert data as follow:

INSERT INTO category (category_no, category_name) VALUES (dbo.generate_category_no(),'BMW');
INSERT INTO category (category_no, category_name) VALUES (dbo.generate_category_no(),'JAGUAR');

One things is that We can call function from INSERT query.

So, when I run the following sql:

SELECT * FROM category;

It give the result as shown in below.

+---+--------------------+--------------+
|id |category_no         |category_name |
+---+--------------------+--------------+
| 1 |CAT-2016-10-000001  | BMW          |
| 2 |CAT-2016-10-000002  | JAGUAR       |
+---+--------------------+--------------+

Thanks everybody for helping me. Thanks!!!

edited Oct 24, 2016 at 10:21

answered Oct 24, 2016 at 4:57

R.Katnaan

2,5364 gold badges28 silver badges38 bronze badges

3 Comments

AGH Over a year ago

can it be handle concurrency?

R.Akhlaghi Over a year ago

When 100 records are sent simultaneously for inset in table, does this function manage concurrency? i have this problem like u

R.Katnaan Over a year ago

For me, it is ok till now. I also tested for more than 1000 records, it is working.

Collectives™ on Stack Overflow

SQL Server: auto-generated custom format sequence number

4 Answers 4

2 Comments

2 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related