generate sequence number. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

generate sequence number.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-08-02 : 10:50:42

krishna writes "Hi,

i am beginner to sql server.i have one problem.
i wrote the stored procedure. in this procedure i want to auto increment the row value. for this i write this query.
declare @processID bigint

set @processID=(select max(ProcessAreaID)+1 from usr_tlb_ProcessArea)

it is working fine but the problenm is.
if the zero rows in the tabl,it's not geting the max value it will return NULL. how i can solve that problem.

one more thing is how can i check the empty colum in the stored procedure .

ex: if exists(select empname from table where empname=@emapname)

the thing is colume is empty again i will pass the empty it will says record is exist.

please give the solution sa soon as posible.

regards,
krishna.v"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-02 : 10:55:56

Try this

set @processID=(select max(isnull(ProcessAreaID,0))+1 from usr_tlb_ProcessArea)

Madhivanan

Failing to plan is Planning to fail

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-02 : 10:56:26

if there is no record you can replace a null with a '1'

set @processID=isnull((select max(ProcessAreaID)+1 from usr_tlb_ProcessArea),1)

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-02 : 10:57:13

quote:
Originally posted by madhivanan

Try this

set @processID=(select max(isnull(ProcessAreaID,0))+1 from usr_tlb_ProcessArea)

Madhivanan

Failing to plan is Planning to fail

thats still null if there are no records in the table...

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-02 : 11:04:19

>>thats still null if there are no records in the table...

Anything wrong here

Declare @t table (i int)
insert into @t values (null)
select max(isnull(i,0)+1) from @t

Madhivanan

Failing to plan is Planning to fail

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-08-02 : 12:56:42

ooo, my favorite. Whats wrong with an IDENTITY column here?

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-02 : 13:38:06

quote:
Originally posted by madhivanan

>>thats still null if there are no records in the table...

Anything wrong here

Declare @t table (i int)
~~insert into @t values (null)~~ --This counts as a record
select max(isnull(i,0)+1) from @t

Madhivanan

Failing to plan is Planning to fail

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-03 : 00:50:08

Thats good point Corey. There you are

Madhivanan

Failing to plan is Planning to fail

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-03 : 08:22:11

To the poster.. I would point out that and Identity column would be the best if possible.

I assumed that since you didn't do that, that there was a reason why...

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-03 : 09:22:21

In the days before IDENTITY...

http://weblogs.sqlteam.com/brettk/archive/2004/06/29/1687.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-03 : 09:28:55

Thius code should work:


declare @processID bigint

select  @processID=(select max(ProcessAreaID)+1 from usr_tlb_ProcessArea)

select @processID = isnull(@processID,1)

quote:
Originally posted by AskSQLTeam

krishna writes "Hi,

i am beginner to sql server.i have one problem.
i wrote the stored procedure. in this procedure i want to auto increment the row value. for this i write this query.
declare @processID bigint

set @processID=(select max(ProcessAreaID)+1 from usr_tlb_ProcessArea)

it is working fine but the problenm is.
if the zero rows in the tabl,it's not geting the max value it will return NULL. how i can solve that problem.

one more thing is how can i check the empty colum in the stored procedure .

ex: if exists(select empname from table where empname=@emapname)

the thing is colume is empty again i will pass the empty it will says record is exist.

please give the solution sa soon as posible.

regards,
krishna.v"

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources