Welcome to WebmasterWorld Guest from 54.226.25.231

Forum Moderators: open

Message Too Old, No Replies

SQL Problem

Driving me crazy

     

Red_Eye

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

10+ Year Member



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

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

10+ Year Member



Try this:

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

Red_Eye

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

10+ Year Member



syber,

Thanks you that worked. Knew it would be simple.

 

Featured Threads

Hot Threads This Week

Hot Threads This Month