Incremental 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

SQL Server 2000 Forums

Transact-SQL (2000)

Incremental number

Author

Topic

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2004-07-13 : 16:45:34

This seems like it should be easy, but I'm tearing my hair out here.

I'm trying to recreate this asp code into a storedproc. I eliminated some of it for space - but I think you get the point.

<code>
Do While Not rsq.eof
rsi = conn.Execute("Select Max (ItemNumber) FROM Item WHERE Prefix <> 'H'")
sItem = rsi.Fields.Item(0).Value
sItem = sItem + 1

rsa = conn.Execute("Update Item SET ItemNumber=" & sItem )
rsq.movenext()
Loop

</code>

I cannot seem to get it to increment the itemnumber for me in a stored procedure. This should be EASY!

Somthing like . . .

UPDATE Item SET ItemNumber=(Select MAX(ItemNumber)+1 ) where ItemNumber Is Null AND ProjID=@ID ?????

It does update several rows but with the same itemnumber.

HELP!

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-07-13 : 16:50:28

do the item numbers mean anything, or can they be arbitrary?

If there is no real meaning to the this value (other than it's uniqueness), you could create the itemnumber column with the IDENTITY property. This will create a column that auto-increments with every insert into your table. Look it up in BOL for more details.

-ec

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2004-07-13 : 16:54:00

Nope, there is meaning to these numbers, and not all rows receive a value in the itemnumber field.

So, I really do need to look at the MAX(ItemNumber) + 1 and insert as appropriate.

Thanks for the suggestion!

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 02:37:23

[CODE]
DECLARE @intNextNumber int

SELECT @intNextNumber = MAX(ItemNumber)+1
FROM Item

UPDATE Item
SET @intNextNumber = ItemNumber = @intNextNumber + 1
WHERE ItemNumber IS NULL
AND ProjID=@ID
[/CODE]
Kristen

Girlnet
Yak Posting Veteran

77 Posts

Posted - 2004-07-14 : 08:19:29

Yeah Kristen! Works like a charm! Thanks so much, I spent way too much time on this and don't know that I would have figured it out.

Thanks again!

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 08:24:11

Its definitely a piece of "obscure" syntax. Glad you've got it fixed now.

Krsiten

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources