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 + '

        ORDER BY ' + @OrderByColumn + ' ' + @OrderByDirection + ') AS T1

      ORDER BY ' + @OrderByColumn + ' ' + @ConOrderByDirection + ') AS T2

    ORDER BY ' + @OrderByColumn + ' ' + @OrderByDirection

  END

ELSE

  BEGIN

    -- Current page is not last page

    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   

  END

-- executes query to get selected page

EXEC(@SqlQuery)

SET NOCOUNT OFF

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.