Create a sequence table first
If exists (select 1
From sysindexes
Where id = object_id ('tsyssequence ')
And name = 'in _ sename'
And indid> 0
And indid <1, 255)
Drop index TSysSequence. In_SeName
Go
If exists (select 1
From sysobjects
Where id = object_id ('tsyssequence ')
And type = 'U ')
Drop table TSysSequence
Go
/* ===================================================== ======================================= */
/* Table: TSysSequence */
/* ===================================================== ======================================= */
Create table TSysSequence (
SeName nvarchar (50) not null,
Increment int not null default 1,
CurVal bigint not null default 0
)
Go
If exists (select 1 from sys. extended_properties
Where major_id = object_id ('tsyssequence ') and minor_id = 0)
Begin
Declare @ CurrentUser sysname
Select @ CurrentUser = user_name ()
Execute sp_dropextendedproperty 'Ms _ description ',
'User', @ CurrentUser, 'table', 'tsyssequence'
End
Select @ CurrentUser = user_name ()
Execute sp_addextendedproperty 'Ms _ description ',
'
Simulate oracle Sequence
User maintenance is not allowed, and no one is allowed to modify the value after database initialization.
By default, two sequences named "DID" and "SID" are generated, meaning "Data serial number" and "system serial number ". ',
'User', @ CurrentUser, 'table', 'tsyssequence'
Go
Insert into TSysSequence (SeName, Increment, CurVal) values ('did', 1, 0 );
Insert into TSysSequence (SeName, Increment, CurVal) values ('sid ', 1, 0 );
/* ===================================================== ======================================= */
/* Index: In_SeName */
/* ===================================================== ======================================= */
Create unique index In_SeName on TSysSequence (
SeName ASC
)
Go
Create a stored procedure to complete sequence usage
If exists (select 1
From sysobjects
Where id = object_id ('pgetsequencevalue ')
And type in ('P', 'pc '))
Drop procedure PGetSequenceValue
Go
Create procedure PGetSequenceValue
@ SeName nvarchar (50 ),
@ SeVal bigint out
As
Begin
If not exists (select 1 from TSysSequence where SeName = @ SeName)
Begin
Raiserror ('nonexistent sequence % s', @ SeName)
Return
End
Update TSysSequence set @ SeVal = CurVal + Increment, CurVal = CurVal + Increment where SeName = @ SeName
End
Go
Usage
Declare @ ID1 int
EXEC PGetSequenceValue 'sid ', @ ID1 OUTPUT
Select @ ID1
Declare @ ID2 int
EXEC PGetSequenceValue 'did', @ ID2 OUTPUT
Select @ ID2
