Often in web development (or any kind of application dev for that matter) you find that you need to display an ordered paged set of records to end users. Adhoc queries to the database make this very easy to do. However if you are constrained to only being able to use stored procedures then things become more complicated.

A Note On Using The Application

Now you could have your application code do the heavy lifting as far as paging (ColdFusion is great for working with databases), however even the most powerful application cannot match a database when it comes to retrieving and sorting of data. In addition even if your application could sort though large volumes of data as quickly as the database if your database is on a separate machine to your application, the higher volume network traffic to return an un-paged set of records to your app will quickly become a bottleneck.

Breaking The Problem Down

So how do we create a paged set of records using SQL stored procedures? In order to create a page set of records using a stored procedure in SQL Server we have to accomplish four things.

1. Work out how many total records exist based on some filter criteria.
2. Select all the records matching our criterea.
3. Order and number the records based on some sorting criteria.
4. Return the numbered records in a specified range (e.g. Rows 10 to 20)

Step 1: Getting Total Number Of Matching Records

In order for the application that is receiving the records to know how many pages of data are available we need to send back the total number of records that match the search criteria. This is the simplest part of the problem so let go ahead and create a simple stored proc to get user records.

CREATE PROCEDURE dbo.proc_getUsersPaged 
      @orderCol varchar(50), -- Used later for sorting
      @orderDir varchar(4), -- Used later for sorting
      @startRow int, -- Used later for paging
      @endRow int, -- Used later for paging
      @nameLike varchar(50) = null -- Used to filter
AS

BEGIN
      -- Declare variables
      DECLARE @maxRowNum INT

      -- Get the total number of records matching the filter criteria
      SELECT @maxRowNum = COUNT(*)
      FROM dbo.users
      WHERE 
      --put filtering conditions (excluding row number limits) here
      @nameLike IS NULL OR @nameLike LIKE '%'+ @nameLike + '%'
END
GO

As you can see all we are doing at this stage is defining a variable to stored the total number of records matching our criteria and storing the result of a count(*).

Step 2: Get All The Matching Records

The next step is to get all the records that match our filter criteria.

-- Get all records that match the filter criteria
SELECT
      userid
      ,username
FROM dbo.users
WHERE 1=1
--put filtering conditions (excluding row number limits) here
AND (@nameLike IS NULL OR username LIKE '%'+ @nameLike + '%')

Combining this with the previous code gives us this.

CREATE PROCEDURE dbo.proc_getUsersPaged 
      @orderCol varchar(50), -- Used later for sorting
      @orderDir varchar(4), -- Used later for sorting
      @startRow int, -- Used later for paging
      @endRow int, -- Used later for paging
      @nameLike varchar(50) = null -- Used to optionally filter by username
AS

BEGIN
      -- Declare variables
      DECLARE @maxRowNum INT

      -- Get the total number of records matching the filter criteria
      SELECT @maxRowNum = COUNT(*)
      FROM dbo.users
      WHERE
      --put filtering conditions (excluding row number limits) here
      (@nameLike IS NULL OR @nameLike LIKE '%'+ @nameLike + '%')

      -- Get all records that match the filter criteria
      SELECT
            userid
            ,username
      FROM dbo.users
      WHERE 
      --put filtering conditions (excluding row number limits) here
      (@nameLike IS NULL OR username LIKE '%'+ @nameLike + '%')
END
GO