MSSQL 2k Stored Procedure for Pagination...

Posted by Dan on Jun 19, 2006 @ 12:09 PM

A while back I found a stored procedure that someone was using to create pagination resultsets in SQL Server 2000. While the stored procedure was nice, it didn't allow for the "DISTINCT" keyword—which is necessary for some of my queries. There were also a few other issues I didn't like about the stored procedure, so I re-wrote the stored procedure pretty much from scratch.

In order to support the "DISTINCT" keyword, I had to change the inner query to use a derived table. Since this provides a little more overhead, I added a bit parameter "@Distinct" which inserts the "DISTINCT" keyword into the query and uses the derived table.

Also, on the initial page of results, I simply use the TOP keyword. The previous stored proc used an inner query, which did a TOP 0—which means the code did nothing.

/*
    This stored procedure allows you to paginate a recordset,
    so that you can pull back just a range of rows from a
    query. It works sort of like this:

    SELECT PAGE <x> SIZE <y>
        <Columns>
    FROM
        <Table>
    WHERE
        <Condition>
    ORDER BY
        <Order>

    statement where:
        * <x> is @SetPage (the number of the "page" to display)
        * <y> is @SetPageSize (the number of results on each "page")
        * <Columns> is @Columns
        * <Table> is @Table
        * <Condition> is @SqlWhere
        * <Order> is @SqlOrderBy
*/


create procedure [dbo].[spSelectNextN]
    @TableName varchar(250),
    @Columns varchar(1000),
    @IdentityColumn varchar(64),
    @SetPage int,
    @SetPageSize int,
    @SqlWhere varchar(4000),
    @SqlOrderBy varchar(1000),
    @Distinct bit = 0,
    @TotalRecords int = null output,
    @TotalPages int = null output,
    @CurrentPage int = null output,
    @SqlString nvarchar(4000) = null output

as

declare @SelectStr nvarchar(20)
declare @InnerSql nvarchar(4000)
declare @PreviousRecords int

-- if selecting distinct records, set the select statement
if @Distinct = 1
    set @SelectStr = N'select distinct '
else
    set @SelectStr = N'select '

-- create a query to get the total number of records
declare @GetRecordsSQL nvarchar(4000)
set @GetRecordsSQL = N'set @count = (' + @SelectStr + ' count(' + @IdentityColumn + ') from ' + @TableName
-- if a where clause was used, add the where clause now
if @SqlWhere + '' <> ''
    set @GetRecordsSQL = @GetRecordsSQL + N' where ' + @SqlWhere

set @GetRecordsSQL = @GetRecordsSQL + N')'

-- execute the dynamic SQL statment and return the value of the @count variable
exec sp_executesql @GetRecordsSQL, N'@count int output', @count = @TotalRecords output

-- set the total pages
set @TotalPages = @TotalRecords/@SetPageSize

-- if page size doesn't go into total records evenly, then we need to adjust for another partial page
if (@TotalRecords % @SetPageSize) >
0
    set @TotalPages = @TotalPages + 1

-- if the @SetPage is less than 1, then grab the first page
if @SetPage < 1
    set @SetPage = 1

-- don't allow a page higher than the total number of pages
if @SetPage >
@TotalPages
    set @SetPage = @TotalPages

-- set the current page
set @CurrentPage = @SetPage

set @PreviousRecords = (@SetPageSize * @SetPage) - @SetPageSize


if @Distinct = 1
    begin
        set @InnerSql = N'(select ' + @IdentityColumn +
            ' from (' + @SelectStr + N'top ' +
            cast(@PreviousRecords as nvarchar(32)) +
            N' ' + @Columns +
            N' from ' +
            @TableName
    end
else
    begin
        set @InnerSql = N'(' + @SelectStr + N'top ' +
            cast(@PreviousRecords as nvarchar(32)) +
            N' ' + @IdentityColumn +
            N' from ' +
            @TableName
    end

if @SqlWhere + '' <> ''
    begin
        set @InnerSql = @InnerSql +
            N' where ' +
            @SqlWhere
    end

if @SqlOrderBy + '' <> ''
    begin
        set @InnerSql = @InnerSql +
            N' order by ' +
            @SqlOrderBy
    end

set @InnerSql = @InnerSql + N')'

if @Distinct = 1
    set @InnerSql = @InnerSql + N' as tmpDerived)'

set @SqlString = @SelectStr + N'top ' +
    cast(@SetPageSize as nvarchar(32)) +
    N' ' +
    @Columns +
    N' from ' +
    @TableName +
    N' where 1 = 1'

if @SetPage > 1
    begin
        set @SqlString = @SqlString +
            N' and (' +
            @IdentityColumn +
            N' not in ' +
            @InnerSql +
            N')'
    end

if @SqlWhere + '' <> ''
    begin
        set @SqlString = @SqlString +
            N' and ' +
            @SqlWhere
    end

if @SqlOrderBy + '' <> ''
    begin
        set @SqlString = @SqlString +
            N' order by ' +
            @SqlOrderBy
    end

-- execute the query
exec sp_executesql @SqlString

-- dump out the values for the pagination
print ''
print 'Total Records: ' + cast(@TotalRecords as varchar(32))
print 'Total Pages: ' + cast(@TotalPages as varchar(32))
print 'Current Page: ' + cast(@CurrentPage as varchar(32))
print ''
print 'SQL: '
print @SqlString

return (0)
GO

To use the stored procedure, do the following:

exec spSelectNextN
    'TableName', -- table name
    'column1, column2, column3', -- columns to retrieve
    'column1', -- the primary key in the table
    1, -- the page number to retrieve
    25, -- the number of results per page
    'column2 = 10 and column3 = 135', -- the "where" clause to use
    'column 1 DESC, column3 ASC', -- the "order by" clause to use
    0 -- whether or not distinct records should be returned
UPDATE:

2006-06-30: Fixed bug if where clause was empty or null

Categories: SQL, HTML/ColdFusion, Source Code

24 Comments

  • I think this may work for me but I am having a hard time using it with PHP. When I run this in query analyzer with paramters it seems to work (I get the results properly) but I get an error:
    Line 1: Incorrect syntax near ')'. and these two variables are Null
    @TotalRecords =
            @TotalPages =

    This is the closest I have come to finding a stored proc for pagination. Nice work. Any info on how to use with PHP would be great.
  • Camrat,

    There was a bug if the where clause was empty or null. I've fixed the bug. This affected thing the total record count query.

    -Dan
  • Sweet, now I can not get a where clause to work. It says invalid column name....I looked but I don't get it.

    Thanks for your quick reply.
  • Sorry my fault...I did not enter the where clause correctly. Great Proc.

    Thanks
    Dan
  • Man, that was awesome. After all my work with ROW_NUMBER() OVER (ORDER BY I found this article. Much easier. Thanks
  • Dan, this is a very powerful sproc and I have been using it for a few months now sucessfully. However, I found a strange bug. On the URL i have listed, there are two groups of items listed by group. If you look closely, you will see that there is EXACTLY one duplicate on both lists. The first duplicate is on the first test the last item in group 3 and the first item of group 4. On the second test (a different list) it repeats the last item of group 2 in group 3. Very odd indeed. Thanks again and I hope you are able to find the reproduce the issue I'm referring to.
  • Sorry, I forgot to include the note that I have double checked our database to ensure that neither of these items exists twice. Thanks.
  • Dan,

    I'm not familiar with the following syntax:

    if @SqlWhere + '' <> ''


    Is this just another way to test for NULL?
  • @Darrell:

    Adding an empty string to a NULL variable will make it an empty string. I didn't want to test just against a NULL value in case someone passed in an empty string as a where clause (for example, if you're where clause was being created dynamically and was blank.)

    I suppose I should be just checking the length of the string. At the moment I can't recall which is faster in SQL, but I'd assume checking the length of the string to make sure it's greater than 0 would be a little more efficient.
  • thx, for information
  • Dan, great job. For a single table looks very nice. Is it possible to use several tables with join clauses? What do you think? Cheers.
  • @Marco:

    What I've done in the past is created views for joined tables and used the stored proc over the view. It's actually probably how I *normally* use the stored proc. :)

    You should get better performance out of using a view anyway...
  • Matthew Mitchell's Gravatar
    Matthew Mitchell
    Dan,

    I like your simple stored proc but you did not include any code as to how you do things on the CF side. Can you give me an example of how you call the proc in your CF page and scroll through the pages, etc?
  • Hi Dan, been using this for more than a year and I love it!

    Are there any ways to retrieve the output variables?
  • @Lim:

    Most languages have a way to get to output variables in a stored procedure. In ColdFusion, you use the <cfstoredproc /> tag in conjunction with the <cfprocparam /> tag to set both input *and* output variables to a stored procedure:

    http://livedocs.adobe.com/coldfusion/6.1/htmldocs/...
  • Dan,

    Thank you for sharing this code. It works great with a simple table, but i am having trouble using this with a view as there is no primary key for my view. Also, what if there is a compound primary key?
  • @Rama:

    SQL Server 2000 doesn't offer any good solutions to your problem, which is the reason for the stored procedures--but if you're using SQL Server 2005+ you can use CTE (Common Table Expressions) to solve your problem.

    Trying to paginate a table without some kind of primary key is going to be very difficult in SQL Server without using a #temp table as you need something to track the records that have been shown and which records to show next.

    You can run this stored procedure from Query Analyzer and you can view the SQL that generated. You can then see if you can reuse the core logic to create a solution for you that works with your views and/or compound keys. If that doesn't work, you could use #temp tables but that may lead to performance issues.
  • Hey Dan, my name is Pearson... how you doin?

    I came across your site via a google search for CF Pagenation. Your SP solution looks like it would be a perfect fit for me and my project, thing is I am stumped on how to get it working. Ive been a CF developer for 5+ years and never really got into SP's. I cut and pasted your SP code into my MySQL Editor and it coughs up. Im not sure what edits i need to make to get it to complile correctly.

    On Ben Nadel's site you mentioned

    "I recommend first playing around w/the stored procedure in Query Analyzer--just to get familiar with it. The usage example I give should run fine in Query Analyzer--provided you supply real table information."

    where do i supply my table info? I thought that would all be done on the CF side? Esh, im lost. Any help is very much appriciated. Thank You!

    Pearson
  • @Pearson:

    The cause of many of your problems may be due to the fact that this Stored Procedure is for MSSQL (Microsoft SQL Server) and not MySQL. This means you'll need to make some modifications to get the script to work.

    I'm not a MySQL guy (I've actually never actively done anything in MySQL,) so I'm not going to be of much help. However, most of the script is pretty standard SQL and I think the only thing that you'll be required to change is the call to "exec sp_executesql". The "sp_executesql" is a MSSQL internal stored proc for executing dynamic SQL statements. I'm sure MySQL has an equivalent function--but I'm not sure what it is.

    Other than the calls to sp_executesql I think the code should pretty much port as-is. If you get it working under MySQL, please post your solution for others!
  • Dan, can I accomplish this through using SQL Server 2005 Express Edition? And if so, are there any suggestions you may have on the best way to transfer the data from MySQL to SQL Express? Many many thanks for your help Dan.

    Pearson
  • @Pearson:

    If you're migrating to SQL Server 2005 Express, then I'd just use MSSQL 2005's native CTE (Common Table Expressions) which allows you to do pagination without a stored proc at all. Just search Google for examples--there's plenty out there.

    My guess is my script could be converted to MySQL in some form. It might take you a little investigating and trial and error, but that seems like a faster solution than migrating your database. You certainly can migrate your database if you want, but that can potential lead to bugs related to differences between the two databases.
  • This proc can be used only when a table has a single primary key.
    It will fail when table has composite primary key(say 2 columns act as a combined primary ket).
  • How about a temporary table with two columns column 1:index-pk,column 2: index from paginated table; with a simple inner join and specific conditions to paginate... (the problem will appear when that table is updated... still it is a simplier sollution)
    For mysql i do not think we need to convert this because it is very simple there using limit and offset.
  • Do we have similar code for MYSQL.

Comments for this entry have been disabled.