homepage Welcome to WebmasterWorld Guest from 54.197.19.35
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
SQL Problem
Driving me crazy
Red_Eye




msg:4041263
 10:31 am on Dec 11, 2009 (gmt 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.

 

syber




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

Try this:

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

Red_Eye




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

syber,

Thanks you that worked. Knew it would be simple.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved