Sqlserver模拟Sequence

浏览数：20 / 时间：2015年06月12日

create table Sequence_OA_Merchants_EnterpriseBasic(

      -- ID列为自增列
      SeqID int identity(1,1) primary key,
      -- Sequence值
      SeqVal varchar(1)
)

CREATE FUNCTION fn_GetNextSequenceVal_OA_Merchants_EnterpriseBasic
as
begin
      -- 声明新Sequence值变量
      declare @NewSeqValue int

      -- 设置插入、删除操作后的条数显示取消
      set NOCOUNT ON

      -- 插入新值到Sequence_OA_Merchants_EnterpriseBasic表
      insert into Sequence_OA_Merchants_EnterpriseBasic (SeqVal) values (‘a‘)

      -- 设置新Sequence值为插入到Sequence_OA_Merchants_EnterpriseBasic表的标识列内的最后一个标识值  
      set @NewSeqValue = scope_identity()

      -- 删除Sequence_OA_Merchants_EnterpriseBasic表(不显示被锁行)
      delete from Sequence_OA_Merchants_EnterpriseBasic WITH (READPAST)

-- 返回新Sequence值
return @NewSeqValue
end

declare @abc varchar(10);
exec @abc = proc_GetNextSequenceVal_OA_Merchants_EnterpriseBasic;
select right(replicate(‘0‘,5)+@abc,5);

select * from Sequence_OA_Merchants_EnterpriseBasic
insert into Sequence_OA_Merchants_EnterpriseBasic(SeqVal) values(‘a‘)
select * from Sequence_OA_Merchants_EnterpriseBasic

dbcc checkident(‘Sequence_OA_Merchants_EnterpriseBasic‘,reseed,-1)  重置Identity