生成特定序列號存儲過程

 create table SeqBASIC_SEQ(
 
      -- ID列为自增列
       SeqID int identity(1,1) primary key,
      -- Sequence值
       SeqVal varchar(1)
 )
--DROP table SeqBASIC_SEQ
 create procedure P_GetNewSeqVal
 @CUSTOMER_ID VARCHAR(10),
 @ReturnResult VARCHAR(25) output
 as
 begin
       declare @IndexValue int
       declare @NewSeqValue varchar(25)
       set NOCOUNT ON
       insert into SeqBASIC_SEQ (SeqVal) values ('a')
       set @IndexValue =CAST(scope_identity() AS varchar(5)) 
  set @NewSeqValue=  @CUSTOMER_ID+'-'+ Convert(varchar(8),Getdate(),112) +'-'+ right('00000'+CAST(@IndexValue AS varchar(5)),5)
       delete from SeqBASIC_SEQ WITH (READPAST)
 SET @ReturnResult= @NewSeqValue
end
 
 /*
 *傳入客戶別
 *返回序列字串： 客戶別-日期-5位流水碼
 */
 declare @result varchar(25)
 Exec  P_GetNewSeqVal '10001', @result output
 Print @result
 

 --drop proc P_GetNewSeqVal
 
 /*
 *重置流水號
 */
 create procedure P_Resetting
 as
 begin
  --刪除表
  DROP table SeqBASIC_SEQ
  --重新創建SeqBASIC_SEQ表
  create table SeqBASIC_SEQ(
  -- ID列为自增列
     SeqID int identity(1,1) primary key,
  -- Sequence值
     SeqVal varchar(1)
  )
 end
 
 
 /*
 *調用重置流水碼存儲過程
 */
  exec P_Resetting