Detailed ideas for implementing the order number and flow ticket number (8 bits) in the Stored Procedure

Source: Internet
Author: User
Tags rtrim

This article draws on the great gods in the garden and I just want to make some modifications. If there is something bad, please try again.
First write the Implementation ideas and then paste the code
1. Create a stored procedure, declare the output parameter @ indentNum, and output the order number.
2. Generally, the order number consists of two parts: the date and the subsequent order number. You can view the number of digits after the date based on your needs. This article is 8 digits.
3. Define the function @ date as the date part and assign values.
4. check whether there is data for the current day In the table. query by date. Generally, there is a date in the order table.
5. If the table exists, + 1 is added to the largest order in the table. If not, the First Order Number of the current day is added.
Note: Replicate, a function that is rarely used, is used, because when the maximum order is + 1, the high 0 must be added.
Syntax: Print replicate ('0', 3) ---- three zeros are output. Copy codeThe Code is as follows: USE MyBookShop
-- Delete a stored procedure
IF OBJECT_ID ('proc _ NumIndent ') IS NOT NULL
Drop proc PROC_NumIndent
-- Create a stored procedure for generating the flow ticket number
Create proc PROC_NumIndent
@ IndentNum nvarchar (20) output -- streamline number
-- The date part is the current date.
DECLARE @ date nvarchar (20)
Set @ date = convert (varchar (20), getdate (), 112) -- format: 20130117
-- Determine whether the table contains data of the current day
DECLARE @ CountMax nvarchar (20)
Select @ CountMax = max (id) from testst where convert (varchar (10), rtrim (ltrim (date) = convert (varchar (10 ), rtrim (ltrim (getdate ())))
-- If @ CountMax is not equal to null, the table contains data of the current day.
IF (@ CountMax <> '')
Declare @ num nvarchar (20)
-- Convert the 8-digit rightmost (next) digits of the largest order obtained today to the int type plus one
Set @ num = convert (varchar (20), convert (int, right (@ CountMax, 8) + 1)
-- Use the replicate function to repeat the value of '0' to add a high position.
Set @ num = replicate ('0', 8-len (@ num) + @ num
Set @ indentNum = @ date + @ num
Print @ indentNum
-- If it does not exist, add '123' as the first order of the current day.
Set @ indentNum = @ date + '123'
PRINT @ indentNum
-- The following describes the stored procedure.
DECLARE @ indentNum nvarchar (20)
EXEC PROC_NumIndent @ indentNum

We also hope that you will be able to provide a better way to compare dates.
It is to judge whether two dates are equal, and the database uses the datatime format. However, it is quite painful to judge.
You also want to take the date part instead of the time part for determination. I hope you can talk about it more.

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: 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.