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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.