Naing Win Htun Naing Win Htun - 1 month ago 5
SQL Question

SQL Server: auto-generated custom format sequence number

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!

Answer

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) 
    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!!!

Comments