SQL 2005 写了个类型oracle sequence 的存储过程

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

 

先建张存序列号的表CREATE TABLE [dbo].[Sequence]( [SequenceNO] [bigint] NOT NULL, [date] [datetime] NOT NULL) ON [PRIMARY]

创建存储过程

CREATE PROCEDURE GetSerialNo @no int outputASbegin

if not exists(select 1 from sequence where datediff(dd,date,getdate())=0) begin  set @no=1  insert sequence values(@no,getdate()) endelse

    begin transaction  select @no=max(sequenceNo) +1 from sequence  with(xlock,holdLock) where datediff(dd,date,getdate())=0  update sequence set sequenceNo=@no where datediff(dd,date,getdate())=0  if @@error <> 0    begin    raiserror('Error,can not get SerialNo',16,1)    rollback    return    end  commit transaction endreturn @no