SQL Server Queries - Pagination Using Sql Query

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

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.

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.