Dynamic Sorting with Pagination - SQL Server

 

Preparing Sample Data

Before beginning to go into detail about the pagination, we will create a sample table and will populate it with some synthetic data. In the following query, we will create a SampleFruits table that stores fruit names and selling prices. In the next part of the article, we will use this table.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

CREATE TABLE SampleFruits ( Id INT PRIMARY KEY IDENTITY(1,1) , FruitName VARCHAR(50) , Price INT ) GO INSERT INTO SampleFruits VALUES('Apple',20) INSERT INTO SampleFruits VALUES('Apricot',12) INSERT INTO SampleFruits VALUES('Banana',8) INSERT INTO SampleFruits VALUES('Cherry',11) INSERT INTO SampleFruits VALUES('Strawberry',26) INSERT INTO SampleFruits VALUES('Lemon',4)   INSERT INTO SampleFruits VALUES('Kiwi',14)   INSERT INTO SampleFruits VALUES('Coconut',34) INSERT INTO SampleFruits VALUES('Orange',24)   INSERT INTO SampleFruits VALUES('Raspberry',13) INSERT INTO SampleFruits VALUES('Mango',9) INSERT INTO SampleFruits VALUES('Mandarin',19) INSERT INTO SampleFruits VALUES('Pineapple',22) GO SELECT * FROM SampleFruits

Dynamic Sorting with Pagination

Applications may need to sort the data according to different columns either in ascending or descending order beside pagination. To overcome this type of requirement, we can use an ORDER BY clause with CASE conditions so that we obtain a query that can be sorted by the variables. The following query can be an example of this usage type:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

DECLARE @PageNumber AS INT DECLARE @RowsOfPage AS INT DECLARE @SortingCol AS VARCHAR(100) ='FruitName' DECLARE @SortType AS VARCHAR(100) = 'DESC' SET @PageNumber=1 SET @RowsOfPage=4 SELECT FruitName,Price FROM SampleFruits ORDER BY CASE WHEN @SortingCol = 'Price' AND @SortType ='ASC' THEN Price END , CASE WHEN @SortingCol = 'Price' AND @SortType ='DESC' THEN Price END DESC, CASE WHEN @SortingCol = 'FruitName' AND @SortType ='ASC' THEN FruitName END , CASE WHEN @SortingCol = 'FruitName' AND @SortType ='DESC' THEN FruitName END DESC OFFSET (@PageNumber-1)*@RowsOfPage ROWS FETCH NEXT @RowsOfPage ROWS ONLY

Also, we can change the sort column and sorting direction through the variables for the above query.

Pagination in a Loop

In this example, we will learn a query technique that returns all discrete page results with a single query.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

DECLARE @PageNumber AS INT             DECLARE @RowsOfPage AS INT         DECLARE @MaxTablePage  AS FLOAT         SET @PageNumber=1         SET @RowsOfPage=4         SELECT @MaxTablePage = COUNT(*) FROM SampleFruits         SET @MaxTablePage = CEILING(@MaxTablePage/@RowsOfPage)         WHILE @MaxTablePage >= @PageNumber         BEGIN          SELECT FruitName,Price FROM SampleFruits         ORDER BY Price         OFFSET (@PageNumber-1)*@RowsOfPage ROWS         FETCH NEXT @RowsOfPage ROWS ONLY         SET @PageNumber = @PageNumber + 1         END

For this query, we created a pretty simple formula. At first, we assigned the total row number of the SampleFruit table to the @MaxTablePage variable, and then we divided it into how many rows will be displayed on a page. So, we have calculated the number of pages that will be displayed. However, the calculated value can be a decimal, and for that, we used the CEILING function to round it up to the smallest integer number that is bigger than the calculated number. As a second step, we implemented a WHILE-LOOP and iterated @PageNumber variable until the last page of the number.