SQL Server automatically generates date plus number of serial numbers _mssql

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 begins
：
CREATE PROCEDURE get_new_id
@NEW_ID VARCHAR () 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)
--not enough digits in front of 0
SET @YYYY = REPLICATE (' 0 ', 4-len (@YYYY)) + @YYYY
SET @MM = REPLICATE (' 0 ', 2-len (@MM)) + @MM
SET @DD = REPLICATE (' 0 ', 2-len (@DD)) + @DD
--Remove the existing maximum ID for 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 taken out
IF @NEW_ID is NULL
--Note that there is no number for the current date, then start numbering directly from 1
SET @NEW_ID = (@YYYY + @MM + @DD + ' 00000001 ')
--if it comes out,
：
ELSE
BEGIN
DECLARE @NUM VARCHAR (8)
--Remove the largest number plus 1
：
SET @NUM = CONVERT (VARCHAR, (CONVERT (INT, right (@NEW_ID, 8)) + 1)
-Because of the type conversion, lost 0 of the high, need to fill up
SET @NUM = REPLICATE (' 0 ', 8-len (@NUM)) + @NUM
--Last return date plus number
：
SET @NEW_ID = @YYYY + @MM + @DD + @NUM
End
End
Go
--Perform 20 calls and insert data 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 results