Stored Procedure Tweak - 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)

Stored Procedure Tweak

Author

Topic

mattsmith321
Starting Member

12 Posts

Posted - 2001-07-31 : 02:48:39

I have a stored procedure that needs to be able to return the Profile record that is before the date and closest to the date. However but if the date parameter is before the earliest Profile entry, then it should return the first one (the earliest). Right now, what I have is:

GetProfile(@UserID, @Date)
SELECT TOP 1
ProfileID,
CreateDate,
Height,
...
FROM Profiles
WHERE UserID = @UserID
AND CreateDate <= @Date
ORDER BY CreateDate DESC

IF @@ROWCOUNT = 0
SELECT TOP 1
ProfileID,
CreateDate,
Height,
...
FROM Profiles
WHERE UserID = @UserID
ORDER BY CreateDate ASC

This returns the data I need except it returns two recordsets if it executes the 'IF @@ROWCOUNT = 0' step. So, is there a way to drop the first recordset before executing the second select statement, or should I go back to the drawing board (most likely)? Any direction is appreciated.

Thanks,
Matt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources