Code for generating non-repeated order numbers using SQL Functions

Copy codeThe Code is as follows:
ALTER function [dbo]. [GetOrderNum] (
@ Ebaystockflag varchar (20) // rule letter
)
Returns varchar (100)
AS
BEGIN
Declare @ dt CHAR (8)
Declare @ flag varchar (20)
Set @ flag = 'B' + @ ebaystockflag
Set @ dt = CONVERT (CHAR (8), GETDATE (), 112)
Declare @ max varchar (100)
SELECT @ max = MAX (OrderNumber)
FROM tb_EbayOrder WITH (XLOCK, PAGLOCK)
WHERE OrderNumber like @ flag + '%'
Set @ max = isnull (@ max, @ flag + @ dt + '000') -- The default value is not displayed.
Declare @ a varchar (100)
Declare @ num varchar (10)
Declare @ ordernum varchar (20)
Set @ a = Convert (varchar, right (@ max, len (@ max)-10) -- Part of the number
If (LEFT (@ a, 1) = 0) -- starts with 0
Begin
If (SUBSTRING (@ a, 2, 1) = 0) -- there are two 0
Begin
Set @ num = Convert (varchar, SUBSTRING (@ a, 3, LEN (@ A-2) + 1)

End
Else if (SUBSTRING (@ a, 3, 1) = 0 and SUBSTRING (@ a, 2, 1) = 0) -- there are three 0
Begin
Set @ num = 1

End
Else begin -- only 0
Set @ num = Convert (int, SUBSTRING (@ a, 2, LEN (@ a) + 1)

End
End
Else begin -- case the number is greater than 100
Set @ num = Convert (varchar, @ a + 1)
End

If (LEN (@ num) = 1) -- add 0 for less than three digits after Truncation
Begin set @ num = '00' + @ num end
If (LEN (@ num) = 2)
Begin set @ num = '0' + @ num end
Set @ ordernum = @ flag + @ dt + @ num
Return @ ordernum
END

Call: first lock the table to be operated in the transaction
Copy codeThe Code is as follows:
SELECT * FROM tb_EbayOrder WITH (TABLOCKX)
SELECT * FROM tb_EbayOrderList WITH (TABLOCKX)
SELECT * FROM tb_EbayOrderUserInfo WITH (TABLOCKX)

Call: select dbo. GetOrderNum (@ ebaystockflag)