Stored Procedure to SELECT NEXT x TOP y Records

--
--  sp_selectnextn by:
--      João Melo (joao.melo@inga.min-agricultura.pt)
--      2002-02-06
--
--  This stored procedure implements a kind of:
--
--      SELECT NEXT <x> TOP <y> <Columns> 
--      FROM <Table> 
--      WHERE <Condition> 
--      ORDER BY <Order>"
--
--   statement where: 
--       - <x> is @GroupNumber;
--       - <y> is @GroupSize;
--       - <Colimns> is @Columns;
--       - <Table> is @Table;
--       - <Condition> is @SqlWhere;
--       - <Order> is @SqlOrderBy.
--

CREATE PROCEDURE sp_selectnextn
@TableName VARCHAR(64),
@Columns VARCHAR(1000),
@IdentityColumn VARCHAR(64),
@GroupNumber INT,
@GroupSize INT,
@SqlWhere VARCHAR(1000),
@SqlOrderBy VARCHAR(1000)

AS

DECLARE @SqlString NVARCHAR(1000)
DECLARE @SqlString2 NVARCHAR(1000)
DECLARE @PreviousRecords INT

SET @PreviousRecords = (@GroupSize * @GroupNumber) - @GroupSize

SET @SqlString2 = N'(SELECT TOP ' + 
CAST(@PreviousRecords AS NVARCHAR(32)) + 
N' ' + @IdentityColumn + 
N' FROM ' + 
@TableName

IF @SqlWhere + '' <> ''
BEGIN
SET @SqlString2 = @SqlString2 + 
N' WHERE ' + 
@SqlWhere
END

IF @SqlOrderBy + '' <> ''
BEGIN
SET @SqlString2 = @SqlString2 + 
N' ORDER BY ' + 
@SqlOrderBy
END

SET @SqlString2 = @SqlString2 + N')'

SET @SqlString = N'SELECT TOP ' + 
CAST(@GroupSize AS NVARCHAR(32)) + 
N' ' + 
@Columns + 
N' FROM ' + 
@TableName + 
N' WHERE (' + 
@IdentityColumn + 
N' NOT IN ' + 
@SqlString2 + 
N')'

IF @SqlWhere + '' <> ''
BEGIN
SET @SqlString = @SqlString + 
N' AND ' + 
@SqlWhere
END

IF @SqlOrderBy + '' <> ''
BEGIN
SET @SqlString = @SqlString + 
N' ORDER BY ' + 
@SqlOrderBy
END

EXEC sp_executesql @SqlString
GO