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