CODEWITHSUNDEEP
sqlsql-server-2008
shannu
shannu

Reputation: 187

solution for generating a running sequence number by using stored procedure in sql server 2008

how to generate a running sequence number from 0001 to 9999 by through using a function in sql server, if u call that function in sql server, it should to be provide a running sequence number when ever if it reach 9999 again it should to be reset 0001

can any help on this please to perform this above task

Upvotes: 0

Views: 5065

Answers (5)

kkjj
kkjj

Reputation: 1

if @ItemsCount=0     
begin
                set @generatedSeNumber=1
end
else
   begin

SET @sql= 'insert into Senumber  values(('+@SeNumber+'))'
Exec (@sql)

Upvotes: 0

user158017
user158017

Reputation: 2991

The only way a database can remember a value is if it is stored in a table (in Oracle, you could use a sequence, but SQL Server doesn't use those.) So I would create a single table with a single value and the function would read the value, increment it, and if need be reset it.

Upvotes: 0

user1082916
user1082916

Reputation:

You can simply get the sequence number using following procedure:

CREATE PROCEDURE proc_GetSeqNumber
AS
BEGIN
DECLARE @idt INT
SET @idt = 0
WHILE (@idt < 9999)
BEGIN
    SELECT @idt = @idt + 1
    PRINT @idt
    END
END

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44316

A function can't update, insert or delete, so you can't store the variable and check it's value next time you call the function. So there is no way of doing this using a function.

Upvotes: 1

juergen d
juergen d

Reputation: 204756

There are plenty of good articles about that. Try Google.

For instance see here and there

Upvotes: 1

Related Questions