(Reprint)Simulating the sequence of Oracle in Sql Server by leveraging the identity

Create   proc  sp_CreateSequence 
    ( @sequenceName   varchar ( 40 ),
     @startVal   int   = 1 ,
     @increment   int   = 1 )
AS
BEGIN
    declare   @sqlStmt   varchar ( 100 )
    set  nocount  on
    set   @sqlStmt   =   ' Create Table  '   +   @sequenceName   +  
       '  ( sqVal int IDENTITY ( '   +   convert  ( varchar ( 10 ), @startVal )  +  
     ' , '   +   convert  ( varchar ( 10 ), @increment )  +   ' )) '
    exec  ( @sqlStmt )
    set  nocount  off
end
go
-- ----------------------------------

Create   function  dbo.CurrVal ( @sequenceName   varchar ( 40 ))
returns   int
as
    begin
       return  (IDENT_CURRENT( @sequenceName ))
    end
go
-- -----------------------------------

Create   proc  NextVal ( @sequenceName   varchar ( 40 ) , @nextVal   int  output)
as
begin
    declare   @sqlStmt   varchar ( 100 )
    set  nocount  on
    set   @sqlStmt   =   ' insert  '   +   @sequenceName   +   '  DEFAULT VALUES  '
    exec  ( @sqlStmt )
    SET   @nextVal = IDENT_CURRENT( @sequenceName )
    set  nocount  off
end  
go

转载于:https://www.cnblogs.com/jerryhong/archive/2009/07/22/1528644.html