Paging With SQL Server Stored Procedures In ASP.NET

Data paging is very useful when you work with large amount of data. Instead of confusing your user with thousands or maybe even millions of records you simply show only first page with 10, 20 or 30 records and enable navigation buttons like Next or Previous if user wants to see other pages. Standard ASP.NET server controls, like GridView have built in paging capabilities in two modes: simple or custom.

Simple paging is easy to implement but it always bind complete records set which is not so scalable solution and can take a lot of network traffic and work very slow if you have large table or your web site is on shared hosting or especially if you have a lot of concurrent visitors. More about how to implement simple or custom paging in GridView and ListView you can read in Data Paging in ASP.NET tutorial.

But, which ever server control you use for data presentation, to get efficient and scalable data paging you need to do all your paging logic on SQL Server's side and SQL Server should return only selected page to ASP.NET web application. With logic like this, you get faster solution, need less memory and avoid too much traffic between SQL Server and web server. On SQL Server side, you can do paging on two main ways:

1. By building a dynamic SQL query

2. By using a stored procedure

Both methods are used widely, to find out how to build dynamic SQL queries for paging check SQL Queries For Paging In ASP.NET. In this tutorial we will see some common solution when paging is done with stored procedure.

Paging stored procedure with three nested queries

You can get selected page by using three nested SQL queries. For example to get third page from Customers table where Country column is 'Spain' with 10 records per page and ordered by CompanyName, sql query will be:

SELECT * FROM
   (SELECT TOP 10 * FROM
     (SELECT TOP 30 * FROM Customers WHERE Country = 'Spain' AS T1 ORDER BY CompanyName ASC)
   AS T2 ORDER BY CompanyName DESC)
T3 ORDER BY CompanyName ASC

I made simple stored procedure based on this idea:

CREATE PROCEDURE getSelectedPage
    @TableOrView nvarchar (50),             
    @SelectedPage int,
    @PageSize int,
    @Columns nvarchar(500),
    @OrderByColumn nvarchar(100),
    @OrderByDirection nvarchar(4),
    @WhereClause nvarchar(500)
AS
DECLARE @ReturnedRecords int, @SqlQuery nvarchar(1000), @ConOrderByDirection nvarchar(4)
IF Upper(@OrderByDirection) = 'ASC'
  BEGIN
    SET @ConOrderByDirection = 'DESC'
  END
ELSE
  BEGIN
    SET @ConOrderByDirection = 'ASC'
  END
 
IF @WhereClause <> ''
  BEGIN
    SET @WhereClause = ' WHERE ' + @WhereClause
  END
 
SET @ReturnedRecords = (@PageSize * @SelectedPage)
SET NOCOUNT ON
SET @SqlQuery = N'SELECT * FROM
     (SELECT TOP ' + CAST(@PageSize as varchar(10)) + ' *  FROM
       (SELECT TOP ' + CAST(@ReturnedRecords as varchar(10)) + ' ' + @Columns +
         ' FROM ' + @TableOrView + @WhereClause + '
         ORDER BY ' + @OrderByColumn + ' ' + @OrderByDirection + ') AS T1
       ORDER BY ' + @OrderByColumn + ' ' + @ConOrderByDirection + ') AS T2
    ORDER BY ' + @OrderByColumn + ' ' + @OrderByDirection
EXEC(@SqlQuery)
SET NOCOUNT OFF
GO

So, to use it with previous example you need this much simpler line:

EXEC getSelectedPage 'Customers', 3, 10, '*', 'CompanyName', 'ASC', 'Country = ''Spain'' '

This example returns all columns (specified with 4th parameter '*'). In case you need only few columns you need to separate them with comma, for example 'Column1, Column2, Column3'. In this case, you always need to add column name used in ORDER BY clause too.

Three nested queries approach is very simple but it returns last page incorrectly. Number of returned records on last page will be equal to page size.. For example, if page size is 10 and you have total of 73 records, last page should have only 3 records. But, this query will return last 10 records.

To correct this I added additional variable, named TotalRecords and slightly different SQL query if current page is last page. So, this is more complex, but completely accurate paging stored procedure:

CREATE PROCEDURE getSelectedPage
    @TableOrView nvarchar (50),             
    @SelectedPage int,
    @PageSize int,
    @Columns nvarchar(500),
    @OrderByColumn nvarchar(100),
    @OrderByDirection nvarchar(4),
    @WhereClause nvarchar(500)
AS
SET NOCOUNT ON
DECLARE @ReturnedRecords int, @SqlQuery nvarchar(1000), @ConOrderByDirection nvarchar(4), @TotalPages int, @TotalRecords int
 
-- Finds total records
SET @SqlQuery = N'SELECT @RecCount = COUNT(*) FROM ' + @TableOrView
EXEC sp_executesql @SqlQuery, N'@RecCount int OUTPUT', @RecCount = @TotalRecords OUTPUT
 
-- Checks order direction
IF Upper(@OrderByDirection) = 'ASC'
  BEGIN
    SET @ConOrderByDirection = 'DESC'
  END
ELSE
  BEGIN
    SET @ConOrderByDirection = 'ASC'
  END

-- checks if WHERE clause is needed
IF @WhereClause <> ''
  BEGIN
    SET @WhereClause = ' WHERE ' + @WhereClause
  END

-- Finds number of pages
SET @ReturnedRecords = (@PageSize * @SelectedPage)
SET @TotalPages = @TotalRecords / @PageSize
IF @TotalRecords % @PageSize > 0
  BEGIN
    SET @TotalPages = @TotalPages + 1
  END
 
-- Checks if current page is last page
IF @SelectedPage = @TotalPages
  BEGIN
    -- Current page is last page
    SET @SqlQuery = N'SELECT * FROM
    (SELECT TOP ' + CAST((@TotalRecords % @PageSize) as varchar(10)) + ' *  FROM
      (SELECT TOP ' + CAST(@ReturnedRecords as varchar(10)) + ' ' + @Columns +
        ' FROM ' + @TableOrView + @WhereClause + '

You can use this procedure on the same way like first example.

Stored procedure for paging by using temporary table

Basically, we create one temporary table with one identity column of type int which will be used as row counter. After that, we extract only wanted rows by filtering rows that belong to selected page.

CREATE PROCEDURE getPageWithTempTable
  @TableOrViewName varchar(50),
  @Columns varchar(500),
  @IdentityColumn varchar(50),
  @SortColumn varchar(50),
  @SortDirection varchar(4),
  @SelectedPage int,
  @PageSize int,
  @WhereClause varchar(500)
  AS
 
SET NOCOUNT ON
 
DECLARE @SQLQuery varchar(5000), @StartRecord int, @EndRecord int
 
-- Create temporary table
CREATE TABLE #TempTable (
RowNumber int IDENTITY (1, 1),
row_id int )
 
-- Find first record on selected page
SET @StartRecord = (@SelectedPage - 1) * @PageSize + 1
 
-- Find last record on selected page
SET @EndRecord = @SelectedPage * @PageSize
 
-- Check if there is WHERE clause
IF @WhereClause <>  ''
  BEGIN
    SET @WhereClause = ' WHERE ' + @WhereClause
  END
 
-- Build INSERT statement used to populate temporary table
SET @SQLQuery = 'INSERT  #TempTable (row_id) ' +
' SELECT TOP ' + CAST(@EndRecord AS varchar(20)) + ' ' +
@IdentityColumn + ' FROM ' + @TableOrViewName + ' ' +
@WhereClause + '  ORDER BY ' + @SortColumn + '  ' + @SortDirection
 
-- Execute statement and populate temp table
EXEC (@SQLQuery) 
 
-- Build SQL query to return only selected page
SET @SQLQuery = N'SELECT RowNumber, ' + @Columns +
' FROM #TempTable tmp JOIN ' + @TableOrViewName +
' ON row_id = ' + @TableOrViewName + '.' + @IdentityColumn +
' WHERE RowNumber >= ' + CAST(@StartRecord AS varchar(20)) +
' AND RowNumber <= ' + CAST(@EndRecord AS varchar(20)) +
' ORDER BY RowNumber '
 
-- Return selected page
EXEC (@SQLQuery)
 
-- Delete temporary table
DROP TABLE #TempTable
 
SET NOCOUNT OFF
GO

So, to return data like in previous example we'll use:

EXEC getPageWithTempTable 'Customers', '*', 'CustomerID', 'CompanyName', 'ASC', 2, 10, 'Country = ''Spain'' '

Stored procedure for paging with ROW_NUMBER() function

SQL Server 2005 introduced new ROW_NUMBER() function that makes paging task easier. To achieve paging like in previous examples, get third page from Customers table where Country column is 'Spain' with 10 records per page and ordered by CompanyName stored procedure will look like this:

CREATE PROCEDURE Paging_Customers
(
  @SelectedPage int,
  @PageSize int
)
AS
BEGIN
  WITH CTE_Customers(PageNumber, ContactTitle, ContactName, CompanyName, Phone, Country)
  AS
  (
  SELECT CEILING((ROW_NUMBER() OVER
  (ORDER BY CompanyName ASC
  AS PageNumber, ContactTitle, ContactName, CompanyName, Phone, Country
  FROM Customers
  )
 
SELECT *
FROM CTE_Customers WHERE PageNumber = @SelectedPage
END

Then, we call this procedure (for third page and ten rows per page) with this simple line:

EXEC Paging_Customers 3, 10

As you can see, this is more hard coded solution but it is more optimized and faster. The reason why we could not use the same logic in previous two stored procedures is that SQL Server doesn't allow using of variables in TOP clause. You can't write something like SELECT TOP @PageSize * FROM TableName because you'll get syntax error. Instead of that, on SQL Server 2000 you need to build SQL query as a string and then execute it by using EXEC keyword. The first two examples with TOP keyword will work on any version of SQL Server, but last solution with ROW_NUMBER() function is usually better solution if you use SQL Server 2005.

Conclusion

It is very important to pay attention to every input from your users because it could be potentially dangerous. It is recommended to use SQL parameters when calling stored procedure to avoid possible SQL injection attacks. Hardest task is usually building of complex WHERE clauses in case that you need to enable not only paging but also database search to your users. Because of that, we developed Search Control as specialized solution to make this task easy. Search Control creates simple or complex WHERE clauses by only changing control's properties, supports different database SQL syntaxes, take care about security issues and more.

Please let me know if you have some other interesting idea for paging with stored procedure. Happy programming!

Tutorial toolbar:  Tell A Friend  |  Add to favorites  |  Feedback  |   Google