for those folks who worked with oracle, know that sequence is an internal database object which has it's own methods and properties.
hey, why shouldn't i use this wonderful auto number feature that sql server has ?
well, auto number will give you a good solution to most of your needs that a sequence provided in oracle.
but, here is some conditions where auto number just won't suffice :
- Managing more than one "auto number" in one table - auto number can be defined only at one field.
- Managing the same auto number across multiple tables - the auto number can be asigned only to a specified table.
the solution is simple:
we will create some framework that will allow us to manage sequences and work with them.
first we will create one table to contain the sequences.
CREATE TABLE [dbo].[sysSequences] (
[SequenceName] [char] (30) COLLATE Hebrew_CI_AS NOT NULL ,
[Incrasement] [int] NOT NULL ,
[StartValue] [int] NOT NULL ,
[CurrentValue] [int] NOT NULL
) ON [PRIMARY]
GO
next thing we need to do is create some stored procedures that will handle these sequences.
the create sequence procedure :
CREATE procedure dbo.sp_Create_Sequence
(
@seqName varchar(30),
@Increasement int,
@startValue int
)
as
begin
declare @r int
begin transaction
-- Check if the sequence exists
set @r =( select count(*)
from sysSequences where SequenceName = @SeqName)
if @r <> 0 --if error is raised
begin
goto LogError
end
insert into sysSequences (SequenceName,Incrasement,StartValue,CurrentValue)
values (@seqName,@Increasement,@startValue,@startValue)
commit transaction
goto ProcEnd
LogError:
rollback transaction
RAISERROR ('Can not create sequence with name %s, sequence exists already.',16,1,@seqName)
ProcEnd:
end
once we created the "object" (not really an object, more as a row in the sequence table), lets get his current value
create procedure dbo.sp_SequenceCurrVal
(
@seqName varchar(30)
)
as
begin
declare @rows int
begin transaction
-- Check if the sequence exists
set @rows =( select count(*)
from sysSequences where SequenceName = @SeqName)
-- if no such row
if @rows = 0
begin
goto LogError
end
-- Get the new value
select CurrentValue from sysSequences
where SequenceName = @SeqName
commit transaction
goto ProcEnd
LogError:
rollback transaction
RAISERROR ('sequence named %s, does not exists.',16,1,@seqName)
ProcEnd:
end
and the "get next value" method (very similar to the get current procedure.)
create procedure dbo.sp_SequenceNextVal
(
@seqName varchar(30)
)
as
begin
declare @rows int
begin transaction
-- Check if the sequence exists
set @rows =( select count(*)
from sysSequences where SequenceName = @SeqName)
-- if no such row
if @rows = 0
begin
goto LogError
end
-- Update the sequence increasement
update sysSequences
SET CurrentValue = CurrentValue + Incrasement
where SequenceName = @SeqName
-- Get the new value
select CurrentValue from sysSequences
where SequenceName = @SeqName
commit transaction
goto ProcEnd
LogError:
rollback transaction
RAISERROR ('sequence named %s, does not exists.',16,1,@seqName)
ProcEnd:
end
thats it, done.
now we have some sql server sequences