Maybe not the best but I've been using this for years.

Keen to try the methods posted in the article.

USE [*********]

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

ALTER PROCEDURE [dbo].[ReturnRows]

(

@SQL nvarchar(4000),

@Page int,

@RecsPerPage int,

@ID varchar(255),

@sort varchar(255)

)

AS

DECLARE @STR nvarchar(4000)

SET @STR='Select TOP '+CAST(@RecsPerPage AS varchar(20))+' * FROM ('+@SQL+') T WHERE T.'+@ID+' NOT IN

(select TOP '+CAST((@RecsPerPage*(@Page-1)) AS varchar(20))+' '+@ID+' FROM ('+@SQL+') T9 ORDER BY '+@Sort+') order by '+@Sort

PRINT @STR

EXEC sp_ExecuteSql @STR