I was in need of a sequence function for SQL Server and this article does the trick:
http://www.winnetmag.com/Article/ArticleID/22442/22442.html
As I implemented this example figure 4 with success:
http://www.winnetmag.com/Files/23/22442/Listing_04.txt
However I need it to increment by one each time it's called currently if I run this function like this:
SELECT id FROM fn_sequence(1,1)
It gives me a start value and end value of one which is what I want however I'd like to increment by one each time it's called - is this possible!
If you want to reset it, simply drop the table MySequence
create procedure MySequenceProc
as
begin
declare @val integer
if not exists (select * from dbo.sysobjects where id = object_id(N'MySequence') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
create table MySequence (id integer identity (1,1), dummy bit)
delete from MySequence
insert into MySequence values (1)
select @val = id from MySequence
return @val
end
you call it like this :
declare @value integer
exec @value = MySequenceProc
select @value