[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQl Server Sequence Function Getting To Increment!

Posted on 2004-09-29
4
Medium Priority
?
1,476 Views
Last Modified: 2012-06-27
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!
0
Comment
Question by:dylanone
4 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12184680
The only way it can increment each time is if it stores the value between calls. If you use a global variable, it won't survive a stop/start of SQL Server. You can store the value in a table just for that purpose to overcome that durablity problem. Or, my preference would be to fetch the most recent value from the destination table and increment that for the insert (just to avoid creating a table to hold the variable).
0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 500 total points
ID: 12185029
the following procedure will maintain a sequence number for you - each time it is called it returns the next value in the sequence.
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

0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 12185235
Read the article. I use a table of in  quite regularly. I populate a tabkle of ints with values from 0 up to whatever. Don't understand why they present a  solution hardcoded to be limited to 10K.  There doesn't seem a lot of value to being able to specify the initial value since any user can simply add that value to the sequence of ints starting at 0.

If you need it to persists across session then , as others have said, you need persistent storage so you need a table.
Create a table with and indentity column and insert a record to get a new value from the identity generating mechanism. You can delete any records you create on the same occasion. That will give you a monotonic increasing series. Not guaranteed gap free under operational error though.
0
 

Author Comment

by:dylanone
ID: 12189599
Perfect - exactly what I needed!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

826 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question