With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.
COURSE of the MONTH
12 days, 4 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQl Server Sequence Function Getting To Increment!
Posted on 2004-09-29
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!
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!
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
4 Comments
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).
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
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
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.
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.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed.
Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses
Course of the Month12 days, 4 hours left to enroll
752 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.