Dynamic Paging Technique using SQL Server stored procedures

Here is a completely dynamic version of paging technique in the form of a stored procedure. Just pass in a short SQL statement, the Order By clause, and the start row and end row you'd like to return in the result-set. The stored procedure will process your SQL in the order specified and return only the rows indicated.

The basic idea is to process as few rows as possible; this means finding the starting point, and then returning all rows "past" that starting point until the desired number of rows has been returned.

The stored procedure is called "ReturnPage" and uses the following arguments:

@Select = the select statement to return
@OrderBy = the order by clause; don't include the "ORDER BY" part, just
the columns. You must include ASC or DESC for each column in the sort
@StartRow = the first row to return
@EndRow = the end row to return
Let's start with some examples from northwind:

returnpage 'select contactTitle, City, CustomerID from customers',
'ContactTitle ASC, City DESC, CustomerID ASC', 1, 10

returnpage 'select * from Orders','EmployeeID ASC, OrderDate DESC,
OrderID ASC',12,31

returnpage 'select * from [order details]','productID ASC, Quantity
DESC, OrderID asc',30,45



CREATE PROCEDURE ReturnPage(@Select varchar(1000), @OrderBy
varchar(1000),

@StartRow int, @EndRow int)

AS

BEGIN

declare @ColList varchar(2000);

declare @Where varchar(2000);

declare @i int;

declare @i2 int;

declare @tmp varchar(1000);

declare @dec varchar(1000);

declare @f varchar(100);

declare @d varchar(100);

declare @Symbol char(2);

declare @SQL varchar(5000);

declare @Sort varchar(1000);

set @Sort = @OrderBy + ', '

set @dec = ''

set @Where = ''

set @SQL = ''

set @i = charindex(',' , @Sort)

while @i != 0

begin

set @tmp = left(@Sort,@i-1)

set @i2 = charindex(' ', @tmp)

set @f = ltrim(rtrim(left (@tmp,@i2-1)))

set @d = ltrim(rtrim(substring (@tmp,@i2+1,100)))

set @Sort = rtrim(ltrim(substring (@Sort,@i+1,100)))

set @i = charindex(',', @Sort)

set @symbol = case when @d = 'ASC' then '>' else '<' end + case when @i=0 then '=' else '' end set @dec = @dec + 'declare @' + @f + ' sql_variant; ' set @ColList = isnull(replace(replace (@colList,'>','='),'<' ,'=') + ' and ','' ) + @f + @Symbol + ' @' + @f set @Where = @Where + ' OR (' + @ColList + ') ' set @SQL = @SQL + ', @' + @f + '= ' + @f end set @SQL = @dec + ' ' + 'SET ROWCOUNT ' + convert(varchar(10), @StartRow) + '; ' + 'SELECT ' + substring(@SQL,3,7000) + ' from (' + @Select + ') a ORDER BY ' + @OrderBy + '; ' + 'SET ROWCOUNT ' + convert(varchar(10), 1 + @EndRow - @StartRow) + '; ' + 'select * from (' + @Select + ') a WHERE ' + substring(@Where,4,7000) + ' ORDER BY ' + @OrderBy + '; SET ROWCOUNT 0;' exec (@SQL) END