--
--  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(250),
	@Columns VARCHAR(1000),
	@IdentityColumn VARCHAR(64),
	@GroupNumber INT,
	@GroupSize INT,
	@SqlWhere VARCHAR(4000),
	@SqlOrderBy VARCHAR(1000)

AS

DECLARE @SqlString NVARCHAR(4000)
DECLARE @SqlString2 NVARCHAR(4000)
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
DECLARE @AddSQL NVARCHAR(4000)
SET @AddSQL = N'Select count(*) as CountAll from ' + @TableName + N' Where ' + @SqlWhere
EXEC sp_executesql  @AddSQL
GO