SQL Problem

Driving me crazy

[Red_Eye]

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.

[syber]

Try this:

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

[Red_Eye]

syber,

Thanks you that worked. Knew it would be simple.