[Sqlserver] automatically generates the serial number of the date plus Digit

Requirement: The following serial number must be generated. The first half is a year, month, and day number in yyyymmdd format. The second half is a number that increases sequentially from 1 day, And the digits must be fixed, fill in 0 for the intermediate deficiency.

InSQLServer2000 after testing in the database, use the following code to implement the function:

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

/**//*

My_id

----------------

2006092700000001

2006092700000002

2006092700000003

2006092700000004

2006092700000005

2006092700000006

2006092700000007

2006092700000008

2006092700000009

2006092700000010

2006092700000011

2006092700000012

2006092700000013

2006092700000014

2006092700000015

2006092700000016

2006092700000017

2006092700000018

2006092700000019

2006092700000020

*/

Note: the date in the original yyyymmdd format can be obtained as follows:

Select convert (char (8), getdate (), 112)

-- Output result:

/**//*

--------

20060927

*/