{infiniteZest}
// Articles. Tutorials. Utilities.
Home  |   Search  |   Login  
Categories Skip Navigation Links
New / All
AJAX
Apple
ASP.NET
.NET
Git
Google / Android
Python / IronPython
Miscellaneous
SQL Server
Selecting only the records that make up the current page
Summary
SQL Server 2005 provides a way to obtain a range of records obtained from a SELECT statement using row_number. This article has a stored procedure that gives the records of a given pagesize from given pagenumber.
 
Table of Contents
 

The popular way to obtain only a small set of records from a SELECT query has been to use the TOP key word in the query. For example, to select the first 10 customer records you would use the following query:

select top 10 * from customers

With SQL Server 2005, you can select a range of records from the entire list the select statement would have returned. In order to accomplish this, you need to do the following two things:

  • Create a temporary result set with the row numbers as a column
  • Get only the records that are between certain row numbers

Temporary Result Set

By using the WITH keyword, you can create a temporary result set that is to be used by one SELECT statement (or one INSERT / UPDATE / DELETE). For example, you can create a temporary result set of top 10 records and then get the top 5 out of the returned records:

WITH testCustomers AS (SELECT TOP 10 * FROM Customers)

SELECT TOP 5 * FROM testCustomers;

The above results can be accomplished without WITH, but it illustrates the concept. First line creates a temporary table/result set called testCustomers and the second line selects some records from that temporary table testCustomers. One interesting thing to note here is that there is a semicolon (;) at the end of both the lines, not after the first line. Meaning, this is one single execution -- results from the first line feed into the second line.

Adding row numbers

In the query below, a temporary table is created with all the columns in the Customers table plus a new column -- rownumber (from ROW_NUMBER) -- has been added to the newly created result set.

WITH testCustomers AS

(

      SELECT *,

            ROW_NUMBER() OVER (ORDER BY Created) AS rownumber

      FROM Customers

)

SELECT * FROM testCustomers;

The result set here has been ordered by the Created column (you can order the result set by whatever the criteria you want). And our result set also has a rownumber column in it. Getting just a few records somewhere in the middle is just a matter of using BETWEEN ... AND as shown below:

WITH testCustomers AS

(

      SELECT *,

            ROW_NUMBER() OVER (ORDER BY Created) AS rownumber

      FROM Customers

)

SELECT * FROM testCustomers

      WHERE rownumber BETWEEN 11 AND 20;

The above query gives back only 10 records from rownumber 11 to 20. Again, one interesting thing to note here is that there is only one semi-colon in the above entire query -- it’s one block essentially.

A Paging stored procedure

With a way to return just a few records from somewhere in between the list (as described above), we can parameterize the above result set to give results from a certain page.

The following procedure would do the job; discussion below.

CREATE PROCEDURE dbo.GetCustomers

      @pageNumber int,

      @pageSize int

 AS

 

      -- Make up the row number boundaries

      DECLARE @rowBegin int;

      DECLARE @rowEnd int;

 

      SET @rowBegin = (@pageSize * (@pageNumber - 1)) + 1;

      SET @rowEnd = @pageSize * @pageNumber;

 

      -- Get the required page

      WITH testCustomers AS

      (

            SELECT *,

                  ROW_NUMBER() OVER (ORDER BY Created) AS rownumber

            FROM Customers

      )

      SELECT * FROM testCustomers

            WHERE rownumber BETWEEN @rowBegin AND @rowEnd;

     

      RETURN 

We have parameterized the stored procedure with a pageNumber (e.g. 2 for 2nd page, 5 for 5th page, etc.) and pageSize (e.g. 5 customers per page, 10 customers per page, etc.)

      @pageNumber int,

      @pageSize int

From the returned list of customers, we need to make up the row boundaries. Meaning, if we want the records from 2nd page where each page has 5 customers, we need to return the rows from 6 to 10. The following logic takes care of that:

      -- Make up the row number boundaries

      DECLARE @rowBegin int;

      DECLARE @rowEnd int;

 

      SET @rowBegin = (@pageSize * (@pageNumber - 1)) + 1;

      SET @rowEnd = @pageSize * @pageNumber;

And now make up the temporary table and return just the records you want -- see the @rowBegin and @rowEnd in the WHERE clause.

      -- Get the required page

      WITH testCustomers AS

      (

            SELECT *,

                  ROW_NUMBER() OVER (ORDER BY Created) AS rownumber

            FROM Customers

      )

      SELECT * FROM testCustomers

            WHERE rownumber BETWEEN @rowBegin AND @rowEnd;

A Paging stored procedure that returns the total record count

Above stored procedure returns the records that make up current page. Typically, we would also need the count of all the records. We can return that value as an OUTPUT parameter. This number helps the front-end set up the navigation controls. For example, say there are 20 records in the system, we asked the above stored procedure to return page 4, where the page size is 5. The stored procedure will send back 5 records, and if we know total number of records we can, for example, disable the Next navigation button on the front-end.

With output parameter for this count, we don’t have to query the database again. That stored procedure will look like the following -- the customerCount will also be returned along with a page full of records.

CREATE PROCEDURE dbo.GetCustomers

    @customerCount int OUTPUT,

    @pageNumber int,

    @pageSize int

 AS

 

      -- Make up the row number boundaries

      DECLARE @rowBegin int;

      DECLARE @rowEnd int;

 

      SET @rowBegin = (@pageSize * (@pageNumber - 1)) + 1;

      SET @rowEnd = @pageSize * @pageNumber;

 

      -- Get the required page

      WITH testCustomers AS

      (

            SELECT *,

                  ROW_NUMBER() OVER (ORDER BY Created) AS rownumber

            FROM Customers

      )

      SELECT * FROM testCustomers

            WHERE rownumber BETWEEN @rowBegin AND @rowEnd;

    

      -- Get the count of all records

      SELECT @customerCount=(SELECT COUNT(*) FROM Customers);

 

    RETURN

 

The following article describes how to add a navigation bar to a repeater (it uses the stored proedure described here):

 

http://www.infinitezest.com/articles/how-to-add-paging-and-navigation-bar-to-a-repeater.aspx

 

 

Bookmark and Share This

More Articles With Similar Tags
Selecting the top x records from a query using one order by clause and then ordering the records with in that set of records becomes easy with the WITH keyword in SQL Server 2005. This article looks at creating a temporary table and then querying on that table.
About  Contact  Privacy Policy  Site Map