I am using MS SQL. I have a table with the following columns
ItemID int (AutoNumber)
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,
DECLARE @Ref int
SET @REF=(SELECT MAX(ISNULL((REF)+1,1)) FROM Table)
INSERT INTO Table(Title, Link, Ref) VALUES (@Title, @Link, @Ref)
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.