How to Hack MySQL Paging Functionality into Microsoft SQL Server

LIMIT and OFFSET in MS SQL Server

Web developers using PHP and MySQL have a crucial piece of functionality that classic ASP developers working with Microsoft SQL Server don’t. It’s pagination. Using MySQL’s LIMIT and OFFSET commands you can very easily add pagination to recordsets that you want to display on web pages. If you’ve spent any amount of time searching for a way to do this with ASP and SQL Server, you know that the code is pretty hard to find. You’re lucky if you find it at all. The few tutorials on the ‘net tend to be overly complicated and pretty bad hacks, usually involving convoluted and resource-intensive subqueries on top of subqueries. This solution is certainly not the best, and it, too, is a resource hog, but it’s the only one I’ve got, so I’m sharing.

The sample stored procedure will query a Microsoft SQL Server “music” table for records. It returns two recordsets. The first recordset contains the meta data you’ll need to add your buttons or drop-down combo box navigation in ASP. The second recordset contains the data. (Tip: Use set rs = rs.nextrecordset to move to the second recordset in ASP.)

As a bonus I’m also including the code to sort the results, something I’ve not been able to find anywhere else.

create procedure dbo.rptPagedResults
@page int,
@perpage int,
@sortby int,
@ascdesc bit
as
set nocount on

/*
parameters:
@page:  the page you want returned (If you have 100 records and you want to display 25 records on each web page, there will be a total of 4 pages.  The @page parameter lets the stored procedure know which page you want.)
@perpage:  the number of rows to return
@sortby: the integer value of column by which you want your data sorted (If you want to sort by the third column, this would be 3.)
@ascdesc:  whether you want the column sorted ascending or descending (0 = asc, 1 = desc)

example:
dbo.rptPagedResults @page = 1, @perpage = 25, @sortby = 2, @ascdesc = 0
*/

-- It's just a good policy to declare all your stored procedure's internal variables at the top of the code.
declare @ubound int, -- the upper bound of your resultset
@lbound int, -- the lower bound
@pages int, -- the total number of pages (based on the number of results you want per page)
@rows int, -- the total number of records in your table (or query)
@sql varchar(8000), -- need to put the code into a string in order to properly sort it
@ascdescstr varchar(10) -- used for sorting

if @ascdesc = 0
begin
select @ascdescstr = ''
end
else
begin
select @ascdescstr = ' desc'
end

-- You should always initialize every variable in a stored procedure.
select @sql = '',
@ubound = 0,
@lbound = 0,
@pages = 0,
@rows = 0

/*
This example assumes you have a table in your database named "music".
If you want to populate a test with some data, here is the create code:
-- CREATE TABLE [music] (
-- [songid] [int] IDENTITY (1, 1) NOT NULL ,
-- [songname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
-- [artist] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
-- [tracknumber] [int] NOT NULL ,
-- [lastplayed] [smalldatetime] NULL ,
-- [album] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
-- CONSTRAINT [PK_music] PRIMARY KEY  CLUSTERED 
-- (
-- [songid]
-- )  ON [PRIMARY] 
-- ) ON [PRIMARY]
-- GO
You'll have to add the data on your own!
*/

-- Create a temporary table to store your data.
-- If you have a simple table with a continuous identity column, you don't need a temp table.  But if you've been deleting from your table and the identity column is not perfect, it's a good idea to just build a temp table.
create table #results(
resultid int identity,
songid int,
songname varchar(50),
artist varchar(50),
tracknumber int,
lastplayed smalldatetime,
album varchar(256)
)

select @rows =  count(distinct m.songid), 
@pages = count(distinct m.songid) / @perpage
from dbo.music m (nolock)

if @rows % @perpage <> 0 select @pages = @pages + 1 
if @page < 1 select @page = 1 
if @page > @pages select @page = @pages 

select @ubound = @perpage * @page,
@lbound = @ubound - (@perpage - 1)  

if @lbound < 0 set @lbound = 0

if @ubound > @rows and @page = @pages set @ubound = @rows

select @ubound UBound,
@lbound LBound,
@perpage PerPage,
@pages Pages,
@rows [Rows],
@page PageNum

select @sql = '
select m.songid,
m.songname,
m.artist,
m.tracknumber,
m.lastplayed,
m.album
from dbo.music m (nolock)
order by ' + cast(@sortby as varchar) + @ascdescstr

insert into #results(
songid,
songname,
artist,
tracknumber,
lastplayed,
album
)
exec(@sql)
set rowcount @perPage 

select *
from #results
where resultid between @lbound and @ubound

drop table #results

set nocount off
go

There are 5 comments on this post

  1. Totally! I mean, without my CMP3 codes and the offset Makadoo font scribbler, I doubt WordPress would function under the demands for pagination that my site requires. If only the Alt.Mov inverter was cross-spawned into MySQL’s T-frame originator, we’d all be Ctrl-Alt-tastic!……..Pubes.

  2. Really, your script is very useful. I used it in my procedure, its working as needed. Thanks alot for your work.

    But I wonder if my database has some ten thousand records, then there will be load on Server because it has to fetch ten thousand records every time into temp table, just to retrieve only 10 records. Really “LIMIT”less SQL Server

  3. So true Manda, its a type of solution but theres a performance hit while you database grows. Almost as bad as using the viewstate!

  4. Hi,
    In your stored procedure:

    set rowcount @perPage

    isn’t usefull , you limit the rows with @lbound and @ubound

Add to the discussion:

I'll never share your email address and it won't be published.

What Is This?

davidgagne.net is the personal weblog of me, David Vincent Gagne. I've been publishing here since 1999, which makes this one of the oldest continuously-updated websites on the Internet.

bartender.live

A few years ago I was trying to determine what cocktails I could make with the alcohol I had at home. I searched the App Store but couldn't find an app that would let me do that, so I built one.

Hemingway

You can read dozens of essays and articles and find hundreds of links to other sites with stories and information about Ernest Hemingway in The Hemingway Collection.