The code below creates the table by name 'sequences'. This table will be used to hold the next value that can be used for a particular sequence
Expand|Select|Wrap|Line Numbers
- CREATE TABLE sequences (
- seq varchar(100) primary key,
- sequence_id int
- );
Expand|Select|Wrap|Line Numbers
- CREATE PROCEDURE nextval
- @sequence varchar(100),
- @sequence_id INT OUTPUT
- AS
- -- return an error if sequence does not exist
- -- so we will know if someone truncates the table
- set @sequence_id = -1
- UPDATE sequences
- SET @sequence_id = sequence_id = sequence_id + 1
- WHERE seq = @sequence
- RETURN @sequence_id
kind regards,
ghd