Welcome to WebmasterWorld Guest from 54.162.250.227

Forum Moderators: open

Message Too Old, No Replies

SQL Problem

Driving me crazy

     
10:31 am on Dec 11, 2009 (gmt 0)

Full Member

10+ Year Member

joined:Sept 24, 2002
posts: 214
votes: 0


I am using MS SQL. I have a table with the following columns

ItemID int (AutoNumber)
Title string
Link string
Ref int

The ref column allows the user to set the order of the items.
So when I insert a new item, I need to get the highest ref from the table and add 1.

however if there are no items in the table it needs to know that the ref is 1. I have been using the following stored procedure

@ItemID int OUTPUT,
@Title nvarchar(50),
@Link nvarcahr(50)

DECLARE @Ref int

SET @REF=(SELECT MAX(ISNULL((REF)+1,1)) FROM Table)

INSERT INTO Table(Title, Link, Ref) VALUES (@Title, @Link, @Ref)

SELECT ItemID=@@IDENTITY

Which works fine once I have data in the table but not if the table in empty. Any suggestions as to what I am doing wrong.

1:37 pm on Dec 11, 2009 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 12, 2004
posts: 393
votes: 0


Try this:

SET @REF=(SELECT ISNULL(MAX(REF)+1),1) FROM Table)

2:13 pm on Dec 11, 2009 (gmt 0)

Full Member

10+ Year Member

joined:Sept 24, 2002
posts: 214
votes: 0


syber,

Thanks you that worked. Knew it would be simple.

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members