Technical Solution

Dynamic Server Side Pagination on SQL Server

Zyxware Technologies | | 3 min read

When retrieving large number of records from a database table it becomes essential to do some kind of pagination while presenting the data to the end user. There are two ways of doing this. One is a server side implementation and another is a client side implementation. Here we are presenting a completely dynamic server side pagination implementation where the client only has to tell the server the number of records the server has to return and the starting record number.

The original implementation is by Jeff Smith. Jeff's version of the procedure is available at http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx. Some slight modifications were made in the stored procedure to handle nulls in the where condition. Also this version uses the Top N strategy to get the top N records.

CREATE PROCEDURE DBO.CREATE_RST_PAGINATE(@Select VARCHAR(5000),
@OrderBy VARCHAR(1000),
@StartRow INT,
@EndRow INT)
AS
BEGIN

DECLARE @ColList VARCHAR(5000);
DECLARE @Where VARCHAR(5000);
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 @NullBoundaryCheck VARCHAR(1000);
DECLARE @SQL VARCHAR(8000);
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, 1000)))
SET @Sort = RTRIM(LTRIM(SUBSTRING(@Sort, @i+1, 1000)))
SET @i = CHARINDEX(',', @Sort)
SET @symbol = CASE WHEN @d = 'ASC' THEN '>' ELSE '<' END +
CASE WHEN @i = 0 THEN '=' ELSE '' END
SET @NullBoundaryCheck = CASE WHEN @i = 0
THEN CASE WHEN @d = 'ASC' THEN '@' ELSE '' END + @f + ' IS NULL'
ELSE
'@' + @f + ' IS ' + CASE WHEN @d = 'DESC' THEN '__NOT__' ELSE '' END + ' NULL AND ' +
@f + ' IS ' + CASE WHEN @d = 'ASC' THEN '__NOT__' ELSE '' END + ' NULL'
END
SET @dec = @dec + 'DECLARE @' + @f + ' SQL_VARIANT; '
SET @ColList = ISNULL(REPLACE(REPLACE(REPLACE(@colList, '>', '='), '<', '='), '__NOT__', '') + ' AND ', '') +
'((@' + @f + ' IS NOT NULL AND ' + @f + ' IS NOT NULL AND ' + @f + @Symbol + '@' + @f + ') OR (' + @NullBoundaryCheck + '))'
SET @Where = @Where + ' OR (' + @ColList + ') '
SET @SQL = @SQL + ', @' + @f + '= ' + @f
--PRINT @COLLIST
--PRINT @WHERE
--PRINT @SQL
END
SET @Where = REPLACE(@Where, '__NOT__', 'NOT')
SET @SQL = @dec + ' ' +
'SET ROWCOUNT ' + CONVERT(VARCHAR(10), @StartRow) + '; ' +
'SELECT ' + SUBSTRING(@SQL,3,7000) + ' FROM (' + @Select + ') A ORDER BY ' +
@OrderBy + '; ' +
'SET ROWCOUNT 0;' +
'SELECT TOP ' + CONVERT(VARCHAR(10), (1 + @EndRow - @StartRow)) + ' * FROM (' + @Select + ') A WHERE ' +
SUBSTRING(@Where,4,7000) + ' ORDER BY ' + @OrderBy + '; '
--PRINT @WHERE
--PRINT @SQL
EXEC (@SQL)
END
GO

Create a stored procedure using the above code and then call from your application as

CREATE_RST_PAGINATE 'SELECT * FROM USERS', 'USERNAME ASC, CREATEDON DESC', 11,21

The first parameter is the select query, the second parameter is the set of sort conditions on columns that can be used as a combined primary key. The select query should be the query that returns the complete table. The third and fourth parameters are the starting row number and the ending row number respectively. The stored procedure was developed for SQL Server but this should be adaptable for MySQL or PostgreSQL. We will be posting a working version for MySQL soon

 

 

Related Topics