Adam Howitt's Blog

Oct 10
2006

Pagination made easier with SQL Server 2005

In the past recordset pagination with SQL Server was like standing on your head and trying to juggle. I wrote a pagination stored procedure for SQL Server 2000 which takes the top x of the reversed recordset then the top y of that. The trouble is that SQL Server 2000 doesn't allow the number of records to pass into the top command as a variable hence the silly EXEC for dynamic sql statements.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_cms_select_paging_amh]
/*********************************************************************************
--** Stored Procedure Name: usp_cms_select_paging_amh
--********************************************************************************
--**
--** Description:
--**   Alternative to standard CMS paging
--** Revision History:
--**   AMH 06.01.05
--**   Updated to account for functional sort criteria e.g. CAST(a.attribute_desc as varchar(2000) DESC
--**   Extended notes:
   I've fixed the paging query again. The issue is caused when you pass the sort order argument a
   function as one of the fields to sort by, for example:
   CAST(a.attribute_desc AS varchar(8000))',
   Which was getting incorrectly interpreted due to the extra spaces (the mask it originally used
   was "alias.column_name sort_direction". Now it correctly acknowledges the sort order in this case.
--** Example:
DECLARE
   @fieldNames varchar(1000),
   @tableName varchar(1000),
   @orderby varchar(100),
   @page int,
   @numRows int,
   @orderbyField varchar(100)
SET   @page = 2
SET    @numRows = 2
SET   @fieldNames = '*'
SET   @tableName = '(select top 3 * FROM vw_attorney_biography) ab'
SET    @orderby = 'ab.name DESC'
SET   @orderbyField = 'name'
EXEC usp_cms_select_paging_amh @fieldNames, '', @tableName, @page, @numRows, 1, '', @orderby, ''
*/
(
   @strFields VARCHAR(4000) ,
   @strPK VARCHAR(100),
   @strTables VARCHAR(4000),
   @intPageNo INT,
   @intPageSize INT,
   @blnGetRecordCount BIT,
   @strFilter VARCHAR(8000) ,
   @strSort VARCHAR(8000),
   @strGroup VARCHAR(8000)
)
AS
BEGIN
   DECLARE
       @topPage int,
       @theSql varchar(2000),
       @theCount varchar(2000),
       @blnBringAllRecords BIT,
       @strPageNo VARCHAR(50),
       @strPageSize VARCHAR(50),
       @strTopPage VARCHAR(50),
       @strFilterCriteria VARCHAR(8000),
       @strSimpleFilter VARCHAR(8000),
       @strSortCriteria VARCHAR(8000),
       @strGroupCriteria VARCHAR(8000),
       @oppositeSort1 VARCHAR(8000),
       @oppositeSort2 VARCHAR(8000),
       @intRecordcount INT,
       @intPagecount INT,
       @errMessage varchar(3000)
   --vars for sort reversing
   DECLARE @this varchar(255),
       @rest varchar(8000),
       @pos int,
       @this_alias varchar(100),
       @this_column varchar(100),
       @this_direction varchar(100),
       @num_rows int
   --vars for recordcount
   DECLARE @totalRecordCount int,
       @ParmDefinition NVARCHAR(500),
       @SQLString NVARCHAR(4000)
   --********* CALCULATE THE LAST ROW TO SHOW ***************
   SET   @topPage = @intPageNo * @intPageSize
   --******** NORMALIZE THE FILTER AND SORTING CRITERIA
   --if they are empty, we will avoid filtering and sorting, respectively, executing more efficient queries
   IF @strFilter IS NOT NULL AND @strFilter != ''
       BEGIN
           SET @strFilterCriteria = ' WHERE ' + @strFilter + ' '
           SET @strSimpleFilter = ' AND ' + @strFilter + ' '
       END
   ELSE
       BEGIN
           SET @strSimpleFilter = ''
           SET @strFilterCriteria = ''
       END
   IF @strSort IS NOT NULL AND @strSort != ''
   BEGIN
       SET @oppositeSort1 = ''
       SET @oppositeSort2 = ''
       --CREATE A STRING WHICH REVERSES THE SORT ORDER
       SET @this = ' '
       SET @rest = @strSort
       SET @pos = PATINDEX('%,%', @rest)
       WHILE (@pos > 0)
       BEGIN
           set @this=ltrim(rtrim(substring(@rest,1,@pos-1)))
           set @rest=ltrim(rtrim(substring(@rest,@pos+1,len(@rest)-@pos)))
           -- function(alias.column text)
           IF PATINDEX('%(%.%',@this) > 0
           BEGIN
               IF PATINDEX('% asc%',lower(@this)) > 0
               BEGIN
                   SET @this_direction = 'asc'
                   SET @this = ltrim(rtrim(substring(@this,1,PATINDEX('% asc%',@this)-1)))
               END
               ELSE IF PATINDEX('% desc%',lower(@this)) > 0
               BEGIN
                   SET @this_direction = 'desc'
                   SET @this = ltrim(rtrim(substring(@this,1,PATINDEX('% desc%',@this)-1)))
                   PRINT 'here3'
               END
               ELSE
                   SET @this_direction = 'asc'
               IF PATINDEX('%.%',@this) = 0
               BEGIN
                   SET @this_alias = ''
                   SET @this_column = ltrim(rtrim(substring(@this,PATINDEX('%.% %',@this)+1,PATINDEX('% %',@this)-PATINDEX('%.% %',@this))))
               END
               ELSE
               BEGIN
                   SET @this_alias = ''
                   SET @this_column = ltrim(rtrim(substring(@this,PATINDEX('%.% %',@this)+1,PATINDEX('% %',@this)-PATINDEX('%.% %',@this))))
               END
           END
           -- alias.column dir        e.g. SELECT PATINDEX('%.% %','ab.hat this')
           ELSE IF PATINDEX('%.% %',@this) > 0
           BEGIN
               SET @this_alias = ltrim(rtrim(substring(@this,1,PATINDEX('%.% %',@this)-1)))
               SET @this_column = ltrim(rtrim(substring(@this,PATINDEX('%.% %',@this)+1,PATINDEX('% %',@this)-PATINDEX('%.% %',@this))))
               SET @this_direction = ltrim(rtrim(right(@this,len(@this)-PATINDEX('% %',@this))))
           END
           -- alias.column        e.g. SELECT PATINDEX('%.%','ab.hat this')
           ELSE IF PATINDEX('%.%',@this) > 0
           BEGIN
               SET @this_alias = ltrim(rtrim(substring(@this,1,PATINDEX('%.%',@this)-1)))
               SET @this_column = ltrim(rtrim(right(@this,len(@this)-PATINDEX('%.%',@this))))
               SET @this_direction = 'ASC'

           END
           -- column direction        e.g. SELECT PATINDEX('% %','ab this')
           ELSE IF PATINDEX('% %',@this) > 0
           BEGIN
               SET @this_alias = ''
               SET @this_column = ltrim(rtrim(substring(@this,1,PATINDEX('% %',@this)-1)))
               SET @this_direction = ltrim(rtrim(right(@this,len(@this)-PATINDEX('% %',@this))))

           END
           -- column only        e.g. SELECT PATINDEX('%.% %','ab.hat')
           ELSE
           BEGIN
               SET @this_alias = ''
               SET @this_column = @this
               SET @this_direction = 'ASC'

           END
           -- add this flipped column to the opposite sort list
           IF len(@oppositeSort1) != 0
           BEGIN
               SET @oppositeSort1 = @oppositeSort1 + ', '
               SET @oppositeSort2 = @oppositeSort2 + ', '
           END
           IF UPPER(@this_direction) = 'ASC'
           BEGIN
               SET @oppositeSort1 = @oppositeSort1 + @this_column + ' DESC'
               SET @oppositeSort2 = @oppositeSort2 + @this_column + ' ASC'
           END
           ELSE
           BEGIN
               SET @oppositeSort1 = @oppositeSort1 + @this_column + ' ASC'
               SET @oppositeSort2 = @oppositeSort2 + @this_column + ' DESC'
           END
           SET @pos= PATINDEX('%,%', @rest)
       END
       set @this=@rest
       -- NOTE: IDENTICAL CODE TO ABOVE
       -- function(alias.column text)
       IF PATINDEX('%(%',@this) > 0
       BEGIN
           IF PATINDEX('% asc%',lower(@this)) > 0
           BEGIN
               SET @this_direction = 'asc'
               SET @this = ltrim(rtrim(substring(@this,1,PATINDEX('% asc%',@this)-1)))
           END
           ELSE IF PATINDEX('% desc%',lower(@this)) > 0
           BEGIN
               SET @this_direction = 'desc'
               SET @this = ltrim(rtrim(substring(@this,1,PATINDEX('% desc%',@this)-1)))
               PRINT 'here3'
           END
           ELSE
               SET @this_direction = 'asc'
           IF PATINDEX('%.%',@this) = 0
           BEGIN
               SET @this_alias = ''
               SET @this_column = ltrim(rtrim(substring(@this,PATINDEX('%.% %',@this)+1,PATINDEX('% %',@this)-PATINDEX('%.% %',@this))))
           END
           ELSE
           BEGIN
               SET @this_alias = ''
               SET @this_column = ltrim(rtrim(substring(@this,PATINDEX('%.% %',@this)+1,PATINDEX('% %',@this)-PATINDEX('%.% %',@this))))
           END
       END
       -- alias.column dir        e.g. SELECT PATINDEX('%.% %','ab.hat this')
       ELSE IF PATINDEX('%.% %',@this) > 0
       BEGIN
           SET @this_alias = ltrim(rtrim(substring(@this,1,PATINDEX('%.% %',@this)-1)))
           SET @this_column = ltrim(rtrim(substring(@this,PATINDEX('%.% %',@this)+1,PATINDEX('% %',@this)-PATINDEX('%.% %',@this))))
           SET @this_direction = ltrim(rtrim(right(@this,len(@this)-PATINDEX('% %',@this))))
       END
       -- alias.column        e.g. SELECT PATINDEX('%.%','ab.hat this')
       ELSE IF PATINDEX('%.%',@this) > 0
       BEGIN
           SET @this_alias = ltrim(rtrim(substring(@this,1,PATINDEX('%.%',@this)-1)))
           SET @this_column = ltrim(rtrim(right(@this,len(@this)-PATINDEX('%.%',@this))))
           SET @this_direction = 'ASC'

       END
       -- column direction        e.g. SELECT PATINDEX('% %','ab this')
       ELSE IF PATINDEX('% %',@this) > 0
       BEGIN
           SET @this_alias = ''
           SET @this_column = ltrim(rtrim(substring(@this,1,PATINDEX('% %',@this)-1)))
           SET @this_direction = ltrim(rtrim(right(@this,len(@this)-PATINDEX('% %',@this))))

       END
       -- column only        e.g. SELECT PATINDEX('%.% %','ab.hat')
       ELSE
       BEGIN
           SET @this_alias = ''
           SET @this_column = @this
           SET @this_direction = 'ASC'

       END
       -- add this flipped column to the opposite sort list
       IF len(@oppositeSort1) != 0
       BEGIN
           SET @oppositeSort1 = @oppositeSort1 + ', '
           SET @oppositeSort2 = @oppositeSort2 + ', '
       END
       IF UPPER(@this_direction) = 'ASC'
       BEGIN
           SET @oppositeSort1 = @oppositeSort1 + @this_column + ' DESC'
           SET @oppositeSort2 = @oppositeSort2 + @this_column + ' ASC'
       END
       ELSE
       BEGIN
           SET @oppositeSort1 = @oppositeSort1 + @this_column + ' ASC'
           SET @oppositeSort2 = @oppositeSort2 + @this_column + ' DESC'
       END
       --SET @strOppositeSort = ' ORDER BY ' +
       SET @strSortCriteria = ' ORDER BY ' + @strSort + ' '
   END
   ELSE
       SET @strSortCriteria = ''
   IF @strGroup IS NOT NULL AND @strGroup != ''
       SET @strGroupCriteria = ' GROUP BY ' + @strGroup + ' '
   ELSE
       SET @strGroupCriteria = ''
   --****    GET THE RECORDCOUNT           *****
   IF @strGroupCriteria != ''
   BEGIN
       SET @ParmDefinition = N'@internalCount int OUTPUT'
       SET @SQLString = 'SELECT @internalCount = COUNT(*) FROM (SELECT COUNT(*) FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)'

       exec sp_executesql @SQLString, @ParmDefinition, @internalCount = @totalRecordCount OUT
       IF (@@error!=0)
       BEGIN
           SET @errMessage = 'Error in dynamic sql clause - SELECT COUNT(*) AS SP_RECORDCOUNT FROM (SELECT COUNT(*) FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)'
           RAISERROR 20001 @errMessage
                RETURN(1)
       END
   END
   ELSE
   BEGIN
       SET @ParmDefinition = N'@internalCount int OUTPUT'
       SET @SQLString = 'SELECT @internalCount = COUNT(*) FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria

       exec sp_executesql @SQLString, @ParmDefinition, @internalCount = @totalRecordCount OUT
       IF (@@error!=0)
       BEGIN
           SET @errMessage = 'Error in dynamic sql clause - SELECT COUNT(*) AS SP_RECORDCOUNT FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria
                RAISERROR 20001 @errMessage
           RETURN(1)
       END
   END
   --******** NORMALIZE THE PAGING CRITERIA
   --if no meaningful inputs are provided, we can avoid paging and execute a more efficient query, so we will set a flag that will help with that (blnBringAllRecords)
   IF @intPageNo < 1
       SET @intPageNo = 1
   SET @strPageNo = CONVERT(VARCHAR(50), @intPageNo)

   IF @intPageSize IS NULL OR @intPageSize < 1 -- BRING ALL RECORDS, DON'T DO PAGING
       SET @blnBringAllRecords = 1
   ELSE
       BEGIN
           SET @blnBringAllRecords = 0
           SET @strPageSize = CONVERT(VARCHAR(50), @intPageSize)
           SET @strPageNo = CONVERT(VARCHAR(50), @intPageNo)
           SET @strTopPage = CAST(@topPage as varchar(5))
           IF @totalRecordCount - (@intPageSize * @intPageNo ) < 0
           BEGIN
               SET @strPageSize = cast(@totalRecordCount - ((@intPageNo - 1) * @intPageSize) as varchar(50))
               --SET @strPageSize = cast(1 as varchar(5))
           END
       END
   --************************** NOW START DOING THE REAL WORK
   IF @blnBringAllRecords = 1 --ignore paging and run a simple select
   BEGIN

       EXEC (
           'SELECT ' + @strFields + ' FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria + @strSortCriteria
       )

   END-- WE HAD TO BRING ALL RECORDS
   ELSE       --BRING ONLY A PARTICULAR PAGE
   BEGIN
       IF @intPageNo = 1 --in this case we can execute a more efficient query, with no subqueries
       BEGIN
           EXEC (
               'SELECT TOP ' + @strPageSize + ' ' + @strFields + ' FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria + @strSortCriteria
           )
           IF (@@error!=0)
           BEGIN
               SET @errMessage = 'Error in dynamic sql clause - SELECT TOP ' + @strPageSize + ' ' + @strFields + ' FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria + @strSortCriteria
               RAISERROR 20001 @errMessage
                    RETURN(1)
           END
       END
       ELSE --execute a structure of subqueries that brings the correct page
       BEGIN
           EXEC (
               'SELECT * FROM (SELECT TOP '+ @strPageSize +' * FROM (SELECT top ' + @strTopPage + ' ' + @strFields +' FROM '+ @strTables + @strFilterCriteria + @strGroupCriteria + @strSortCriteria + ') rev_tb ORDER BY '+ @oppositeSort1 + ') page ORDER BY '+ @oppositeSort2
           )
           --SELECT @strTopPage
           --SELECT 'SELECT * FROM (SELECT TOP '+ @strPageSize +' * FROM (SELECT top ' + @strTopPage + ' ' + @strFields +' FROM '+ @strTables + @strFilterCriteria + @strGroupCriteria + @strSortCriteria + ') rev_tb ORDER BY '+ @oppositeSort1 + ') page ORDER BY '+ @oppositeSort2
           IF (@@error!=0)
           BEGIN
               SET @errMessage = 'Error in dynamic sql clause - SELECT * FROM (SELECT TOP '+ @strPageSize +' * FROM (SELECT top ' + @strTopPage + ' ' + @strFields +' FROM '+ @strTables + @strFilterCriteria + @strGroupCriteria + @strSortCriteria + ') rev_tb ORDER BY '+ @oppositeSort1 + ') page ORDER BY '+ @oppositeSort2
               RAISERROR 20001 @errMessage
                    RETURN(1)
           END
       END

   END --WE HAD TO BRING A PARTICULAR PAGE

   --IF WE NEED TO RETURN THE RECORDCOUNT
   IF @blnGetRecordCount = 1
       SELECT @totalRecordCount AS SP_RECORDCOUNT

END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

As you can see, it's a monster but it's pretty darn fast to run and allows you to pass in the tables, fields and sort order you need without having to rewrite the craziness for every new pagination query.

SQL Server 2005 makes this considerably easier with the new function

row_number() OVER (ORDER BY mycol)

which allows returns the current row number so you can grab the middle of the recordset as follows:

DECLARE @rowsPerPage int,
@pageNum int,
@startRow int,
@endRow int

SET @rowsPerPage = 10
SET @pageNum = 3

SET @startRow = ((@pageNum- 1) * @rowsPerPage)+1
SET @endRow = @startRow + @rowsPerPage -1

SELECT * FROM (
SELECT row_number() OVER (ORDER BY id) as resultNum, id
FROM regTrackingHistory
) as numberResults
WHERE resultNum BETWEEN @startRow AND @endRow

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)

[Add Comment] [Subscribe to Comments]

I am the first to admit that I know ZERO about stored procedures but that looks wholy over whelming!!! I do all of my pagination on the ColdFusion side of things. I do have to return some extra data (only IDs), but I find the queries much more simple. Plus, most of the time when I paginate I have to join to at least one table. ... is that easy to do in a stored procedure with EXEC()? Is that what the @tablename is? Can you basically pass in any SELECT statement as the table name? If that is so, then that is pretty darn cool.
Anyway, it looks huge but most likely very useful. I wish i knew more about stored procedures

Posted By Ben Nadel : Posted At 10/10/06 4:17 PM
Here is the SP I use for paging in SQL Server 2000. A little longer than your 2005 example, but a little shorter than the 2000 code!
Best Wishes, Peter
CREATE PROCEDURE systemsforge_getlist_by_page( @TableName VARCHAR(50), @FieldList VARCHAR(500), @PrimaryKey VARCHAR(25), @OrderBy VARCHAR(100), @PageSize INT, @PageIndex INT = 1, @QueryFilter VARCHAR(200) = NULL ) AS SET NOCOUNT ON
DECLARE @SizeString AS VARCHAR(5) DECLARE @PrevString AS VARCHAR(5)
SET @SizeString = CONVERT(VARCHAR, @PageSize) SET @PrevString = CONVERT(VARCHAR, @PageSize * (@PageIndex - 1))
IF @QueryFilter IS NULL OR @QueryFilter = '' BEGIN
EXEC( 'SELECT ' + @FieldList + ' FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN (SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' NOT IN (SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' ORDER BY ' + @OrderBy + ') ORDER BY ' + @OrderBy + ') ORDER BY ' + @OrderBy ) EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName)
END ELSE BEGIN
EXEC( 'SELECT ' + @FieldList + ' FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN (SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' AND ' + @PrimaryKey + ' NOT IN (SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' ORDER BY ' + @OrderBy + ') ORDER BY ' + @OrderBy + ') ORDER BY ' + @OrderBy ) EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName + ' WHERE ' + @QueryFilter)
END
RETURN 0

GO

Posted By Peter Bell : Posted At 10/10/06 4:35 PM
Wow, I still can't fathom why paging is so terrible in SQL Server. Both of those paging examples look like they belong in thedailywtf.com. It's easy on pretty much any other database platform.

Posted By Brandon Harper : Posted At 10/10/06 4:38 PM
If you guys are posting your procedure solutions, I might as well post a link to my ColdFusion pagination solution:
http://www.bennadel.com/index.cfm?dax=blog:281.view
Cheers, Ben

Posted By Ben Nadel : Posted At 10/10/06 4:45 PM
Thanks for the code Peter, Ben. Ben - part of the beauty of the stored procedures I suggested and the one Peter gave is that it is one stored procedure to paginate any number of recordsets. You simply call the stored proc with the FROM clause, the list of fields to return and then the ORDER BY list and it does the rest. I took a look at your solution and see that it also pulls back all 500 records (at least if I follow correctly) before it eliminates those it doesn't need. The pagination stored procs here work for tables with millions of results if necessary and only ever return the number of rows you request e.g. 10 or 100 at a time. I could probably whip up the CFSTORED PROC example for you to replace your query if you wanted to give it a shot and are running SQL2k.

Posted By Adam Howitt : Posted At 10/10/06 4:57 PM
Oh, just realized the difference in the code peter - does yours work with multiple sort columns with different sort directions e.g. last_name ASC, first_name DESC. The extra code in mine tries to get the correct reverse of last_name DESC, first_name ASC...

Posted By Adam Howitt : Posted At 10/10/06 5:02 PM
Adam, you are partially correct about my code. It pulls back all the required IDs (ex. 500 ID values)... but then it hits the Database again to full data for only X number of records. So, yeah, the down side is that I have to pull down all matching IDs. It's a fairly minimal amount of data when compared with the full query and in most cases (that I work on ) it is fairly fast.
I have never worked on tables that have millions of records, and this is probably a topic for another time... but should you really be paginating through millions of records. In my Administrtive tools, I rarely allow the database to return more than maybe 5000 records (TOP 5000). If you are running a query that gets more than 5000 and need to paginate, then you need to narrow your search criteria. Just my 2 cents on that....
Pete, I would very interested to know how stored procedure pagination fits into your whole iterating business object model (IBO). Does this conflict? Or is it totally irrelivant?

Posted By Ben Nadel : Posted At 10/10/06 5:05 PM
Hi Ben, With reference to your point on 5000 records, I agree that silly requests from people who want every widget description mention the word "the" should be met with Monty Python style maniacal laughter but in reality many clients insist that their application should be that flexible. Keyword searches are typically the killer in terms of pagination and the best I can usually offer is the first page of results (always super fast since it is just one top command) but then let the app server provide a polite note that there were over 500 results matching the request and perhaps they might have more luck being a little more specific. Of course you can remove my British sarcasm from the error message but it typically works.
Adam

Posted By Adam Howitt : Posted At 10/10/06 5:17 PM
Adam, true, some clients are sometimes crazy like that. I suppose it's time I learn more about stored procedures anyway :)

Posted By Ben Nadel : Posted At 10/10/06 6:19 PM
Hi Ben,
Re: the IBO, I am adding the ability to paginate, filter and sort to the IBO so that for reasonably sized recordsets (up to a few hundred) I don't have to go back to the database as often. Also I MUST have that feature as in the database you can only page and order by database fields or by values that can be easily derrived in database fields using SQL. If oyu have an extremely complex set of business rules to determine the price for each product, you can't order, filter or page by price using a stored procedure (assuming you don't want all of the business rules in your SP).
At the same time I do have tables with LOTS of records and use the paginating SP as an option for those use cases. I have two base DAO methods: getByFilter() and getByFilterPaged() which cover these use cases and for now I let the service layer decide which to use for a given table or query.

Posted By Peter Bell : Posted At 10/10/06 8:47 PM
Well, I might as well jump in with my solution:
http://blog.pengoworks.com/blogger/index.cfm?action=blog:546
It pulls out only the required data and allows for multiple order by statements.
It even allows for the use of DISTINCT keyword queries.
Where my approach differs is it pulls the top X records from a table, but filters out the top Y records.
So, it looks like:
/* this would show records 31-40 */ select top 10 -- the number of records to show from table where pKey not in ( select top 30 from table )

Posted By Dan G. Switzer, II : Posted At 10/11/06 9:11 AM
Thanks Dan.

Posted By Adam Howitt : Posted At 10/11/06 10:06 AM
It just proves there are always multiple solutions to the same problem.
The funny thing is looking at my code got me wondering why I'm not using the NOT EXISTS keywords instead of the NOT IN directive.
Unfortunately, I don't remember if I tested performance between the two methods! Looks like something to test for when I have some free time.
Either way, I'm definitely looking forward to row_number() in MSSQL 2005. There's been plenty of times when I've wanted that value to solve a problem. I just wonder under the hood what performance issues there might be.

Posted By Dan G. Switzer, II : Posted At 10/11/06 10:19 AM
Adam,
I had previously written that I never let clients return TOO many search results. But then, it just dawned on me where I ask a site to return so much content. Flickr. I love just randomly jumping around Flickr and that has millions of photos most likely. So, there is a very real-world example for me where there is a need to paginate though an enourmous amount of records. In my experience though, when you start getting up into large page numbers, the pagination starts breaking. In fact, just now, when i tested, every page above 1000 returns the same exact photos (even though the page index changes). So anyway, i just wanted to say that I do, afterall, see where large record sets need to be paginated.
Peter, thanks for the feedback about the pagination and IBO and all this stuff.

Posted By Ben Nadel : Posted At 10/11/06 3:18 PM
--try this
declare @display_rows int declare @ubound int declare @lbound int declare @pages int declare @rows int
set @display_rows = 10
select @rows = count(*), @pages = count(*) / @display_rows from bookings b where type = 1
if @rows % @display_rows != 0 set @pages = @pages + 1 if @pagenum < 1 set @pagenum = 1 if @pagenum > @pages set @pagenum = @pages
set @ubound = @display_rows * @pagenum set @lbound = @ubound - (@display_rows - 1)
declare @booking_date datetime
set ROWCOUNT @lbound
select @booking_date = b.booking_date from bookings b where b.type = 1 order by b.booking_date
set ROWCOUNT @display_rows
select b.col1, b.col2, b.col3 from bookings b order by b.booking_date
set ROWCOUNT 0
select CurrentPage = @pagenum, TotalPages = @pages, TotalRows = @rows

Posted By Mike : Posted At 10/17/06 8:45 PM
Hi Adam !
I´m from Brasil and like your blog. My english is very worst, but this post is very very very good.
Congratulations !
Thanks!

Posted By Mateus : Posted At 7/5/07 9:59 PM
Adam,
I was working on a similar problem and came up with a slightly different twist on paged records. Here's my post about it:
http://blog.emergentpath.com/index.cfm/2007/12/7/Paging-record-sets-in-SQL-Server-2005

Posted By Rob Munn : Posted At 12/7/07 11:35 PM
Yes it's easier. But I wouldn't say that it's easy:
http://codecrafter.blogspot.com/2008/08/painful-sql-server-pagination.html

Posted By Jordão : Posted At 8/4/08 1:35 PM
I am new to store prodecures.
how can i add this code to my ASP Page. Thank you
DECLARE @rowsPerPage int, @pageNum int, @startRow int, @endRow int
SET @rowsPerPage = 10 SET @pageNum = 3
SET @startRow = ((@pageNum- 1) * @rowsPerPage)+1 SET @endRow = @startRow + @rowsPerPage -1
SELECT * FROM ( SELECT row_number() OVER (ORDER BY id) as resultNum, id FROM regTrackingHistory ) as numberResults WHERE resultNum BETWEEN @startRow AND @endRow

Posted By JD : Posted At 9/15/08 2:34 PM
How do you determine the @pagenum

Posted By Travus : Posted At 8/14/09 12:09 AM
Hi Adam, I am not that much good in SQL Server 2005, Can you help me how can i use pagination for the below query which fetch all the required data from multiple tables SQL Query is: SELECT [case].case_id AS case_id, CONVERT(VARCHAR,bu.milestone_actual_date,110) AS Business_Unit_Acceptance_Actual_Date, u.full_name AS Compliance_User, CONVERT(VARCHAR,ca.milestone_target_date,110) AS Compliance_Approval_Milestone_Date, [case_status_type].case_status_type_name AS Case_Status, l.full_name AS Business_Unit_User, CONVERT(VARCHAR,ca.milestone_actual_date,110) AS Compliance_Approval_Actual_Date, [case].citation_number AS Citation_Number, CONVERT(VARCHAR,bu.milestone_target_date,110) AS Business_Unit_Acceptance_Milestone_Date, [business_unit].business_unit_name AS Business_Unit, [case_type].case_type_name AS Case_Type, [case].examination_name AS Examination_Name, [case].document_source AS Document_Source, CONVERT(VARCHAR,[case].date_introduced,110) AS Date_Introduced, CONVERT(VARCHAR,[case].date_received,110) AS Date_Received, [case].document_reference_id AS Document_Reference_ID, case violation_repeated when 0 then 'No' when 1 then 'Yes' else '' end AS Repeat_Violation_Indicator, [document_type].document_type_name AS Document_Type, CONVERT(VARCHAR,[case].date_effective,110) AS Effective_Date, [case].risk_assessment AS Risk_Assessment, [case].examination_number AS Examination_Number, [case].priority_assessment AS Priority_Assessment, [jurisdiction].jurisdiction_name AS Jurisdiction, [case].violation_risk_level AS Violation_Risk_Level
FROM [case] INNER JOIN [customer_company] ON [case].customer_company_id = [customer_company].customer_company_id INNER JOIN [case_type] ON [case].case_type_id = [case_type].case_type_id INNER JOIN [lsw_usr] u ON [case].case_owner = u.[user_name] LEFT OUTER JOIN [jurisdiction] ON [case].jurisdiction_id = [jurisdiction].jurisdiction_id LEFT OUTER JOIN [document_type] ON [case].document_type_id = [document_type].document_type_id LEFT OUTER JOIN case_business_unit ON [case].case_id = [case_business_unit].case_id LEFT OUTER JOIN business_unit ON [case_business_unit].business_unit_id = [business_unit].business_unit_id AND [business_unit].customer_company_id = [customer_company].customer_company_id INNER JOIN (SELECT [case_status_history].case_id as case_id, [case_status_history].case_status_type_id, [case_status_history].case_business_unit_id FROM [case_status_history] JOIN (SELECT MAX(csh.[datetime]) AS maxdatetime, csh.case_id FROM [case_status_history] csh where csh.case_business_unit_id IS NOT NULL AND csh.[datetime] >= '12-04-2009 12:00:00 AM' AND csh.[datetime] <= '03-04-2010 11:59:59 PM' GROUP BY csh.case_id, csh.case_business_unit_id) AS cs_sq3 ON cs_sq3.maxdatetime = [case_status_history].[datetime] AND cs_sq3.case_id = [case_status_history].[case_id]) AS cs_sq4 ON [case_business_unit].case_business_unit_id = cs_sq4.case_business_unit_id INNER JOIN case_status_type ON case_status_type.case_status_type_id = cs_sq4.case_status_type_id
LEFT OUTER JOIN (select case_id, milestone_actual_date, milestone_target_date, business_unit_id from milestone where milestone_type_id = 5) AS ca ON [case].case_id = ca.case_id AND [business_unit].business_unit_id = ca.business_unit_id left outer JOIN ( SELECT [case_status_history].case_id as case_id, [case_status_history].case_business_unit_id as case_business_unit_id, [case_status_history].username as username FROM [case_status_history] JOIN (SELECT MAX(csh.[datetime]) AS maxdatetime FROM [case_status_history] csh WHERE charindex('_bui_', csh.from_group) > 0 GROUP BY csh.case_id, csh.case_business_unit_id) AS sq1 ON sq1.maxdatetime = [case_status_history].[datetime] ) as sq2 ON [case].case_id = sq2.case_id AND [case_business_unit].case_business_unit_id = sq2.case_business_unit_id FULL OUTER JOIN lsw_usr l ON sq2.username = l.[user_name] LEFT OUTER JOIN (select case_id, milestone_actual_date, milestone_target_date, business_unit_id from milestone where milestone_type_id = 1) AS bu ON [case].case_id = bu.case_id AND [business_unit].business_unit_id = bu.business_unit_id
WHERE [customer_company].customer_company_name = 'wk_cc_ABC_Company_lr' AND ([case_type].case_type_id = '1' OR [case_type].case_type_id = '3' OR [case_type].case_type_id = '2')

Posted By utjal : Posted At 3/10/10 10:53 AM

[Add Comment]

Adam Howitt's Blog

Pagination made easier with SQL Server 2005

NAVIGATION

Search

Tags

RSS

Subscribe

Recent Entries

Recent Comments

Archives By Subject