Is it possible in SQL Server to create a function that could handle a sequence?
We are looking at various options for moving our persistence layer from Oracle to another database, and we are looking at MS SQL. However, we use Oracle sequences throughout the code, and it seems like movement will be a headache because of this. I understand about @identity, but it will be a major overhaul of the persistence code.
Is it possible in SQL Server to create a function that could handle a sequence?
source to share
It depends on your use of sequences in Oracle. Typically, the sequence is started in an Insert trigger.
From your question, I am assuming it is a persistence layer that generates a sequence before inserting into the database (including a new pk)
In MSSQL, you can combine SQL statements with ';', so to get the identity column of the newly created record, use INSERT INTO ...; SELECT SCOPE_IDENTITY ()
Thus, the command to insert a record returns a recordset with one row and one column containing the value of the identity column.
You can of course include this approach and create Sequence tables (similar to a double table in Oracle), something like this:
INSERT INTO SequenceTable (dummy) VALUES ('X');
SELECT @ID = SCOPE_IDENTITY();
INSERT INTO RealTable (ID, datacolumns) VALUES (@ID, @data1, @data2, ...)
source to share
I did this last year on a project. Basically, I just created a table with the sequence name, current value, and the sum of the increments.
Then I created 4 procs:
- GetCurrentSequence (sequenceName)
- GetNextSequence (sequenceName)
- CreateSequence (sequenceName, startValue, incrementAmount)
- DeleteSequence (sequenceName)
But there is a limitation that you cannot appreciate; functions cannot have side effects. Thus, you can create a function for GetCurrentSequence (...), but GetNextSequence (...) must be proc, since you probably want to increment the current value of the sequence. However, if it's a proc, you won't be able to use it directly in your insert statements.
So instead of
insert into mytable(id, ....) values( GetNextSequence('MySequence'), ....);
Instead, you need to split it into 2 lines;
declare @newID int;
exec @newID = GetNextSequence 'MySequence';
insert into mytable(id, ....) values(@newID, ....);
Also, SQL Server doesn't have any mechanism that could do something like
MySequence.Current
or
MySequence.Next
Hopefully someone will tell me that I am wrong with the above restrictions, but I am sure they are accurate.
Good luck.
source to share