Select record, if doesnt exist (been deleted) - select next valid record

I have inherited a database with records that have been deleted. I’m working on a table of news items. This means that there are some missing id’s where these records have been deleted. You are able to open any news story’s in the archive(1开发者_JAVA技巧000’s) then using next and previous buttons to navigate through all of the news stories. At present if you navigate to the next record that has been deleted, a record set end of file is thrown and a default message saying “news item no longer available” is shown. Is there a way to detect this missing record and move to the next valid news story(with id etc)? I'm using old asp for this site, is there a way to detect this while navigating through a record set or will this type of functionality have to come from the database, maybe a trigger? Thanks for any help.

SELECT  TOP 1 *
FROM    news
WHERE   id >= @next_id
ORDER BY
        id

For SQL Server 2005 and above see MSDN.

You can use row_number to create a contiguous number column.

1) Since you know that it affects front-end (your 'record set end of file') of course you can manually increment/decrement identifier and try fetching another record from DB, this seems to be the best that you can do without going into database. But this can be very uneffective if many records in a row are missing. I would advise changing code in database instead.

Assuming you have a query like this:

SELECT * FROM News WHERE Id=@Id

where @Id is identifier which you're trying to fetch. Instead you will have something like this:

SELECT * FROM News WHERE Id=(SELECT MIN(Id) FROM News WHERE Id>=@Id)

This will allow you to select first available record. You should use MAX instead of MIN and <= instead of >= if you will look for 'previous' news item, example above should work for next news item.

Also do not forget that with this approach you will have to increment/decrement identifiers for next/previous records based on the value that you fetched from database instead of that which you were looking for. Example: you have following identifiers in your SQL table - 1,5,12,21. You've opened news item with Id=1. 'Next' button will start looking for Id>=2 and will return record with Id=5. When you will open it then your 'Next' button should look for record with Id>=6 (not 2).

Next point is that you will have to provide not only identifier of record you are trying to fetch but also direction in which to look for. And this parameter should also be passed in http query string.

Also this approach may be not very user-friendly since all pages with following urls will display the same item:
site/news.asp?Id=2&direction=next
site/news.asp?Id=3&direction=next
site/news.asp?Id=4&direction=next
site/news.asp?Id=5&direction=next

2) So maybe it will be more user-friendly to determine which record will be next and previous in advance, when you are displaying current record. In this case you will have to execute query like this:

SELECT (SELECT MAX(Id) FROM News WHERE Id<@Id) as previousId, (SELECT MIN(Id) FROM News WHERE Id>@Id) as nextId  

and then correspondingly update Urls for your 'Next'&'Previous' buttons. So if in previous example we will open news item with Id=5 then 'previous' button will navigate directly to Id=1 and 'Next' button will navigate to Id=12.

I believe the second approach is even better in your case since it can be implemented with less changes and it also allows you implementing graying out 'Next'&'Previous' links if corresponding records are not available (you will know this by having NULL returned by the query for previousId or nextId).

Hope this helps :)