SQL Server automatically generates the serial number of the date plus Digit

USE MASTER
GO
If exists (SELECT * FROM dbo. sysdatabases WHERE
Name = 'my _ test_database ')
Drop database [my_test_database]
GO
Create database [my_test_database]
GO
USE [my_test_database]
GO
Create table [my_table] ([my_id] VARCHAR (16 ))
GO
-- The stored procedure starts.
:
Create procedure get_new_id
@ NEW_ID VARCHAR (16) OUTPUT
AS
BEGIN
DECLARE @ DATE DATETIME
DECLARE @ yyyy varchar (4)
DECLARE @ mm varchar (2)
DECLARE @ dd varchar (2)
-- Save the current time obtained
:
SET @ DATE = GETDATE ()
SET @ YYYY = DATEPART (yyyy, @ DATE)
SET @ MM = DATEPART (mm, @ DATE)
SET @ DD = DATEPART (dd, @ DATE)
-- Fill in the first 0 if the number of digits is not enough
SET @ YYYY = REPLICATE ('0', 4-LEN (@ YYYY) + @ YYYY
SET @ MM = REPLICATE ('0', 2-LEN (@ MM) + @ MM
SET @ DD = REPLICATE ('0', 2-LEN (@ DD) + @ DD
-- Retrieve the largest ID of the current date in the table
SET @ NEW_ID = NULL
Select top 1 @ NEW_ID = [my_id] FROM [my_table] WHERE [my_id] LIKE
@ YYYY + @ MM + @ DD + '%' order by [my_id] DESC
-- If not
IF @ NEW_ID IS NULL
-- If there is no serial number for the current date, the serial number starts from 1.
SET @ NEW_ID = (@ YYYY + @ MM + @ DD + '000000 ')
-- If
:
ELSE
BEGIN
DECLARE @ num varchar (8)
-- Remove the largest number and Add 1
:
SET @ NUM = CONVERT (VARCHAR, (CONVERT (INT, RIGHT (@ NEW_ID, 8) + 1 ))
-- Because the high 0 value is lost after type conversion, it must be supplemented.
SET @ NUM = REPLICATE ('0', 8-LEN (@ NUM) + @ NUM
-- Add the number of the last returned date
:
SET @ NEW_ID = @ YYYY + @ MM + @ DD + @ NUM
END
END
GO
-- Perform 20 calls and data insertion tests
:
DECLARE @ N INT
SET @ N = 0
WHILE @ N <20
BEGIN
DECLARE @ NEW_ID VARCHAR (16)
EXECUTE get_new_id @ NEW_ID OUTPUT
Insert into [my_table] ([my_id]) VALUES (@ NEW_ID)
SET @ N = @ N + 1
END
SELECT * FROM [my_table]
GO
-- Output result