Sequential Number Generation (Format: yyyymmdd + 6-digit serial number)

------------------------------------------------
-- Insert the user's purchase Point Information
------------------------------------------------
Create procedure insertuseraccount
@ Phone nvarchar (15 ),
@ Point int,
@ Status nvarchar (50 ),
@ Step1time datetime,
@ Payby nvarchar (100 ),
@ ID Int = 0 output
As
Set nocount on

--------------------------------------- Generate a serial number as the document number --------------------------------
Declare @ ordid nvarchar (100)
Declare @ ord nvarchar (100)
Declare @ time nvarchar (100)

Select @ ord = isnull (max (right (orderno, 6), '2013') from useraccount where left (orderno, 8) = convert (varchar, getdate (), 000000)
Set @ ord = cast (@ ord as bigint) + 1) as varchar)
If (LEN (@ ORD) <6)
Begin
Set @ ord = '000000' + @ ORD
Set @ ord = substring (@ ord, Len (@ ORD)-5, 6)
End

Set @ time = convert (varchar, getdate (), 112)

Set @ ordid = @ time + @ ORD
------------------------------------------- The serial number is generated ----------------------------------------

Insert [useraccount]
(
Orderno,
Phone,
Point,
Status,
Step1time,
Payby
)

Select
@ Ordid,
@ Phone,
@ Point,
@ Status,
@ Step1time,
@ Payby

Select @ ordid

------------------------------------------------
Go