Copy Code code 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 ')--cannot find the result and give a default value
DECLARE @a varchar (100)
DECLARE @num varchar (10)
DECLARE @ordernum varchar (20)
Set @a=convert (Varchar,right (@max, Len (@max)-10)--intercept the digital portion
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)--three 0
Begin
Set @num =1
End
Else begin--only one 0 of the case
Set @num =convert (int,substring (@a,2,len (@a)) +1)
End
End
Else begin-The case where the number is greater than 100
Set @num =convert (varchar,@a+1)
End
if (LEN (@num) =1)--0 less than three digits after interception
Begin Set @num = ' + ' + @num end
if (LEN (@num) =2)
Begin set @num = ' 0 ' + @num end
Set @ordernum = @flag + @dt + @num
Return @ordernum
End
Call: Locks the table to be manipulated first in a transaction
Copy Code code 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)
