流水号生成（格式：YYYYMMDD+6位流水号）

------------------------------------------------
-- 插入用户买点数信息
------------------------------------------------
CREATE PROCEDURE InsertUserAccount
    @Phone        NVARCHAR(15),
    @Point          int,
    @Status            NVARCHAR(50),
    @Step1Time     DATETIME  ,
    @PayBy       NVARCHAR(100),
    @ID           INT = 0 OUTPUT
AS
    SET NOCOUNT ON

-----------------------------------------生成流水号作为单据号--------------------------------
declare @ordid nvarchar(100)
declare @ord nvarchar(100)
declare @time nvarchar(100)

select @ord=isnull(max(right(orderNO,6)),'000000') from UserAccount  where left(orderNO,8)=CONVERT(varchar,getdate(),112)
set @ord=cast((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
-------------------------------------------流水号生成完毕----------------------------------------

    INSERT [UserAccount]
 (
          orderNO, 
          phone,
                      point,
         status,
                     Step1Time,
                     PayBy
 )

    SELECT
         @ordid, 
        @Phone,
           @Point,
           @Status,
 @Step1Time,
 @PayBy

select @ordid

------------------------------------------------
GO

转载于:https://www.cnblogs.com/Flysir/archive/2006/02/08/327201.html