A method for generating program numbers using SQL stored procedures

In Program During design, especially for enterprise application development, a large number of numbers, such as order numbers and warehouse receiving numbers, cannot be generated. Now the SQL stored procedure can be used to conveniently implement automatic numbering, which can greatly improve program reuse and reduce Code .

The convert function in SQL is used to format the date.
For example, to create a numbering structure like this:
ID (2 bits) date and time (14 bits) sequential number (4 bits)
BH 20070227160954 1001

The program code is as follows:

Declare   @ Myval   Nvarchar ( 20 ),
  @ Maxval   Nvarchar ( 4 )

Select   @ Maxval = Max ( Right (Inequipnum, 4 )) + 1   From Inequip-- Inequipnum is the number field. Take the last 4 digits and Add 1.

Set   @ Myval = Convert ( Varchar ( 12 ), Getdate (), 112 ) +-- Date combination
( Substring ( Convert ( Varchar ( 20 ), Getdate (), 120 ), 12 , 2 )) +-- Take the hour
( Substring ( Convert ( Varchar ( 20 ), Getdate (), 120 ), 15 , 2 )) +-- Minute
( Substring ( Convert ( Varchar ( 12 ), Getdate (), 108 ), 7 , 2 )) + -- Seconds
( Select   Case
When   @ Maxval   Is   Null   Then   ' 1000 '-- If the number is null, a value is given first.
Else   @ Maxval
End
)

Select   @ Myval No.

The result after running is bh200702271609541001.

Appendix convert function usage instructions:
Use convert:
Convert (data_type [(length)], expression [, style])

select convert (varchar, getdate (), 120)
11:06:08
select Replace (replace (convert (varchar, getdate (), 120 ),\'-\', \ '), \', \ '), \': \ ', \')
20040912110608
select convert (varchar (12), getdate (), 111)
2004/09/12
select convert (varchar (12), getdate (), 112)
20040912
select convert (varchar (12), getdate (), 102)
2004.09.12
select convert (varchar (12), getdate (), 101)
09/12/2004
select convert (varchar (12), getdate (), 108)
11:06:08