---

Search posts under SQL Server:

---

Pagination query in SQL Server

How to get paginated records from SQL Server?

To get paginated records from SQL Server, we can use OFFSET xxx ROWS FETCH NEXT xxx ROWS ONLY statements clause.

CREATE PROCEDURE LoadPaginatedRecords
	@startRowIndex int,
	@pageSize int,
	@totalCount int output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

  SELECT * FROM ArticlesCatView WHERE Active = 1 AND IsPublished = 1 ORDER BY 
  UpdatedDateTime DESC OFFSET @startRowIndex ROWS FETCH NEXT @pageSize ROWS ONLY
		
  SELECT @totalCount = Count(AutoID) FROM ArticlesCatView WHERE Active = 1 AND IsPublished = 1 
END
GO

In the above code snippet, we have created a stored procedure named LoadPaginatedRecords that accepts 3 parameters

1. startRowIndex - row index to start fetching records
2. pageSize - number of records to fetch starting from row index
3. totalCount OUTPUT - output parameters that is set with the total number of records in the database. This parameter is used to show page numbers on the users interface by dividing them with page size. Look at this example in asp.net.

How to call LoadPaginatedRecords stored procedure?

To call above created stored procedure, we have declared @totalCount parameter and passing other parameters along with this output parameter 

DECLARE @totalCount int
SET @totalCount = 0
EXEC LoadPaginatedRecords 30, 10, @totalCount out

Above statements gives 10 records 31^st record. Similarly, we can change 1^st and 2^nd parameters to get desired result.