TOP 1 and @@IDENTITY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

TOP 1 and @@IDENTITY

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2002-12-02 : 11:09:59

SELECT TOP 1
@CourseID = CourseID ,
@CourseName = CourseName ,
@CourseStartDate = CourseStartDate
FROM (SELECT TOP 1 * FROM Courses WHERE CourseID > @CourseID ORDER BY CourseID ASC) X

IF @@IDENTITY IS NULL BEGIN

The select above definitely fills in values (selects a row) but leaves @@IDENTITY NULL.

What's the best test to ensure a row was selected, or not?

Test the return values?

Sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-02 : 11:16:17

@@IDENTITY does not change based on a SELECT, it only changes if an INSERT operation occurs.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-02 : 11:28:03

SELECT @CourseID = CourseID ,
@CourseName = CourseName ,
@CourseStartDate = CourseStartDate
FROM (SELECT TOP 1 * FROM Courses WHERE CourseID > @CourseID ORDER BY CourseID ASC) X

if @@rowcount = 1
if @CourseID is not null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SamC
White Water Yakist

3467 Posts

Posted - 2002-12-02 : 11:45:41

I should have figured @@IDENTITY would be difficult to define for a select.

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources