The "next" composite primary key

Everything in a relational database is possible without cursors. Thanks for asking.

Okay, some things are more efficient with a cursor, but those are exceedingly rare. <grin />

You said "SQL v 8.00" which presumably is Microsoft SQL Server, so here's a solution that will work for that particular database system:

declare @searchItem varchar(9)
declare @searchLoc integer

set @searchItem ='I01'
set @searchLoc  =4

select top 1
       itemId, locId
  from yourtable
 where itemid > @searchItem
    or (
       itemid = @searchItem
   and locID  > @searchLoc
       )
order
    by itemId, locId

itemId locId

 I01    10   

This isn't strictly "generic" SQL, since it uses the Microsoft-specific TOP keyword.

For More Information

• Dozens more answers to tough SQL questions from Rudy Limeback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.