Under Stored proc @sequence value increment by 1

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

SQL Server 2000 Forums

SQL Server Development (2000)

Under Stored proc @sequence value increment by 1

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2007-01-05 : 14:02:31

I have a stored proc. in it i am trying to increment by 1 to the max value of sequenceNo, it works fine upto 10 and after 10 onwards it is still giving 10 as a new sequenceno instead of 11.. the datatype i have for sequenceno - Real
Can you please help.....

here is my code in the stored proc, where i am incrementing the value of @Sequence by 1:

*********************************************************
SET @TempSeqNO = (SELECT MAX(SequenceNO) FROM TAB_ccsNetRM
WHERE
ProgID = @ProgID AND
ProjID = @ProjID AND
ContractID = @ContractID)
IF @TempSeqNO is null --NO RM record exist yet for this prog/proj/contract
SET @SequenceNO = 1.0
ELSE
SET @SequenceNO = ROUND(@TempSeqNO,0,1) + 1.0

*********************************************************

Thank you very much for the help.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-05 : 14:46:22

I'd strongly recommend makingthe sequence number an integer and making it an identity column in your table so that you don't have to do this in your stored procedure and you don't get rounding errors.

However, if you really want to do it this way, then this should work better (although I don't see a problem with your code and when I tried it it worked correctly)

SET @SequenceNO = (SELECT MAX(SequenceNO) + 1 FROM TAB_ccsNetRM
WHERE
ProgID = @ProgID AND
ProjID = @ProjID AND
ContractID = @ContractID)
IF @SequenceNO is null --NO RM record exist yet for this prog/proj/contract
SET @SequenceNO = 1.0

If you are still having problems check to see that your query is correct and that you are getting the rows you want based on the WHERE clause you are using.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources