Pagination Using SQL Queries in SQL Server

In SQL Server, you can implement pagination using the `OFFSET` and `FETCH` clauses. This allows you to retrieve a specific number of rows from a query, which is useful for displaying data in a paginated format.

Syntax

The basic syntax for pagination using `OFFSET` and `FETCH` is:

SELECT column1, column2, ... FROM table_name ORDER BY column_name OFFSET offset_value ROWS FETCH NEXT fetch_value ROWS ONLY;

Where:

  • `offset_value` is the number of rows to skip before starting to return rows.
  • `fetch_value` is the number of rows to return.

Example

Let's say we have a table `Customers` with 100 rows, and we want to display 10 rows per page. We can use the following query to retrieve the first 10 rows:

SELECT CustomerID, Name, Email FROM Customers ORDER BY CustomerID OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

This query will return the first 10 rows of the `Customers` table, ordered by `CustomerID`.

Result

CustomerID Name Email
1 John Smith john.smith@example.com
2 Jane Doe jane.doe@example.com

Pagination Links

To create pagination links, you can use the following HTML code:

<a href="?page=1">First</a> <a href="?page=2">2</a> <a href="?page=3">3</a> ... <a href="?page=10">Last</a>

Where `?page=X` is the URL parameter that specifies the page number.

Dynamically Generating Pagination Links

To dynamically generate pagination links, you can use a stored procedure or a SQL query that returns the total number of rows and the current page number. For example:

CREATE PROCEDURE sp_GetCustomersPaginated @page int, @rowsPerPage int AS BEGIN DECLARE @totalRows int; DECLARE @offset int; SET @totalRows = (SELECT COUNT(*) FROM Customers); SET @offset = (@page - 1) * @rowsPerPage; SELECT CustomerID, Name, Email FROM Customers ORDER BY CustomerID OFFSET @offset ROWS FETCH NEXT @rowsPerPage ROWS ONLY; SELECT @totalRows AS TotalRows, @page AS CurrentPage, CEILING(@totalRows / @rowsPerPage) AS TotalPages; END;

This stored procedure takes two parameters: `@page` and `@rowsPerPage`. It returns the paginated data, as well as the total number of rows, the current page number, and the total number of pages.