存储过程生成单据号

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER    PROCEDURE [dbo].[PRO_TICKETNUMBER]

( 

    @tablename    varchar(20),     --表名 

    @outnumber varchar(20) output --返回值

) 

AS 

begin

    DECLARE @SIGNNAME NVARCHAR(20)

    DECLARE @NUMBER NVARCHAR(20)

    DECLARE @TODAY NVARCHAR(20) 

    DECLARE @YESTODAY  VARCHAR(20)

    DECLARE @DAYSTR VARCHAR(20)

    DECLARE @NUM    INT 

    DECLARE @sql    NVARCHAR(200)

     

    set @SIGNNAME = (select SIGN from T_TICKNUMBER where TABELNAME=@tablename)

    set @NUMBER = (select NUMBER from T_TICKNUMBER where TABELNAME=@tablename)

    if @SIGNNAME is null or @NUMBER is null

    set @outnumber='-1';

    else

begin

          set @YESTODAY =SUBSTRING(@NUMBER,1,8)

          set @TODAY=SUBSTRING(CONVERT(VARCHAR(100),GETDATE(),112),1,8)

          if @TODAY = @YESTODAY

   begin

            SET @NUM=SUBSTRING(@NUMBER,9,12)

            SET @NUM=@NUM+1

   end

          else

   set @NUM=1;

    --拼接下一个流水号：J+年份4位+月份2位+4位编号(如J2010120001) 

          SET @NUMBER = @TODAY+SUBSTRING(CONVERT(CHAR(5),10000+@NUM),2,4)

          SET @outnumber=@SIGNNAME+@NUMBER

          UPDATE T_TICKNUMBER SET NUMBER=@NUMBER WHERE TABELNAME =@tablename 

       end

end