LIMIT and OFFSET in MS SQL Server

How to Hack MySQL Paging Functionality into Microsoft 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 never 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

This entry was posted on Friday, September 22nd, 2006 at 5:10 pm and is filed under Programming, Software, web design.

Search Google for similar information. You can also leave a response or trackback from your own site.

Tagged with: code, database, programming, query, sql