SQL: how to select next id given an id

There is one table T ( id integer, primary key ( id).

I want a parameterized query that, given id i:

will return next consecutive id,

if i = biggest id in T, query sh开发者_StackOverflow中文版ould return the smallest id in T (cyclical)

You can select the smallest id over the value @i (if any), and the smallest id, then get the largest:

select max(id)
from (
  select top 1 id
  from T
  where id > @i
  order by id
  union all
  select top 1 id
  from T
  order by id
) x

Or perhaps:

select max(id)
from (
  select min(id) as id
  from T
  where id > @i
  union all
  select min(id)
  from T
) x

This appears to be what you're looking for:

CREATE PROCEDURE dbo.ProcName
(
    @ID INTEGER
)
AS
SELECT TOP 1 id
FROM table
WHERE id > @ID
ORDER BY id