Filtering by OFFSET-FETCH Options in Select query - SQL Server / TSQL Tutorial Part 118

Scenario :

You are working as SQL Server developer with front end development team.The front end team needs to implement pagination.  Confuse about Pagination? No problem. Think about viewing your bank statement or credit card statement. Where applications shows only 10 or 20 records per page  and you have to click next to see next records.That is called pagination.

Now you understand pagination, the front end development needs SQL query from you that can be used to returned required results and they should be able to pass page number to return records.


Solution:

There are multiple ways to write Pagination queries, One of them is by using OFFSET FETCH clause. You have to sort the records if you want to use OFFSET FETCH.

Let's create dbo.TotalSale table and insert some sample records. I have only inserted 11 records.

CREATE TABLE [dbo].[TotalSale]
    (
      [id] [int] NOT NULL ,
      [SalePersonFName] [varchar](100) NULL ,
      [SalePersonLName] [varchar](100) NULL ,
      [ProductName] [varchar](100) NULL ,
      [ItemsSold] [int] NULL ,
      [SoldPrice] [float] NULL ,
      [SoldDate] [date] NULL ,
      [City] [varchar](100) NULL ,
      [State] [varchar](100) NULL ,
      [Country] [varchar](100) NULL ,
      [Region] [varchar](100) NULL
    )

INSERT  [dbo].[TotalSale]
        ( [id], [SalePersonFName], [SalePersonLName], [ProductName],
          [ItemsSold], [SoldPrice], [SoldDate], [City], [State], [Country],
          [Region] )
VALUES  ( 1, N'Aamir', N'Shahzad', N'TV', 1, 700, CAST(N'2015-07-15' AS DATE),
          N'Charlotte', N'NC', N'USA', N'North America' )
  ,     ( 2, N'M', N'Raza', N'Cell Phone', 2, 800, CAST(N'2015-07-15' AS DATE),
          N'Charlotte', N'NC', N'USA', N'North America' )
  ,     ( 3, N'Christy', N'Ladson', N'TV', 3, 1600,
          CAST(N'2015-04-02' AS DATE), N'High Point', N'NC', N'USA',
          N'North America' )
  ,     ( 4, N'John', N'Rivers', N'Laptop', 5, 2400,
          CAST(N'2014-03-09' AS DATE), N'Jersey City', N'NJ', N'USA',
          N'North America' )
  ,     ( 5, N'Najaf', N'Ali', N'Computer', 1, 300,
          CAST(N'2015-06-20' AS DATE), N'Karachi', N'Sindh', N'Pakistan',
          N'Asia' )
  ,     ( 6, N'Sukhjeet', N'Singh', N'TV', 2, 900, CAST(N'2015-06-21' AS DATE),
          N'ChandiGar', N'Punjab', N'India', N'Asia' )
  ,     ( 7, N'Chirag', N'Patel', N'Cell Phone', 5, 1500,
          CAST(N'2015-06-23' AS DATE), N'AhmadAbad', N'Gujrat', N'India',
          N'Asia' )
  ,     ( 8, N'Aleena', N'Aman', N'Laptop', 2, 800,
          CAST(N'2015-05-25' AS DATE), N'Lahore', N'Punjab', N'Pakistan',
          N'Asia' )
  ,     ( 9, N'Petra', N'Henry', N'TV', 10, 5000, CAST(N'2015-04-08' AS DATE),
          N'Paris', N'Île-de-France', N'France', N'Europe' )
  ,     ( 10, N'Rita', N'Roger', N'Laptop', 7, 2100,
          CAST(N'2015-04-11' AS DATE), N'Paris', N'Île-de-France', N'France',
          N'Europe' )
  ,     ( 11, N'Tamara', N'Tony', N'Cell Phone', 2, 1200,
          CAST(N'2015-03-03' AS DATE), N'Frankfurt', N'Hesse', N'Germany',
          N'Europe' )


1) Let's say if we would like to skip first 5 rows and want to show all rest of the rows we can use below query.

Select 
[id], [SalePersonFName], [SalePersonLName], [ProductName],
[ItemsSold], [SoldPrice]
 from dbo.TotalSale 
          order by id
          OFFSET 5 rows
How to use OFFSET FETCH Clause in SQL Server to skip first X rows and show all rest of them - SQL Server Tutorial

2) Now if we would like to show 3 records per page, we can use below query. In this case we are going to show first page

Select 
[id], [SalePersonFName], [SalePersonLName], [ProductName],
[ItemsSold], [SoldPrice]
 from dbo.TotalSale 
          order by id
         OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

How to use OFFSET FETCH clause to return records per page in SQL Server - SQL Server Tutorial

Noticed that I have OFFSET 0, that means that I want to show first page and with 3 rows. If I want to show second page records, I will set to OFFSET 1, the Next 3 ROWS ONLY part will stay the same as want to show only 3 rows per page.

We can use variables so we don't have to make changes in the query and by changing the value of variables, we can return our required results. You can create Stored Procedure if you like by using below query.

Declare @PageNumber int
Declare @RowsPerPage int
set @RowsPerPage=3
SET @PageNumber=1

Select 
[id], [SalePersonFName], [SalePersonLName], [ProductName],
[ItemsSold], [SoldPrice]
 from dbo.TotalSale 
          order by id
         OFFSET (@PageNumber-1)*@RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;


If we need to provide Stored Procedure to Front End team, which accepts page number and number of rows they would like to return for each page, you can use below to create Stored Procedure.

 Create procedure dbo.sp_GetSaleRecordsPerPage
 @PageNumber int,
 @RowsPerPage int
AS 
BEGIN
Select 
[id], [SalePersonFName], [SalePersonLName], [ProductName],
[ItemsSold], [SoldPrice]
 from dbo.TotalSale 
          order by id
         OFFSET (@PageNumber-1)*@RowsPerPage 
         ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
END

Let's say if we would like to return second page with 4 records, we can use the dbo.sp_GetSaleRecordsPerPage by providing below parameter values.

EXEC dbo.sp_GetSaleRecordsPerPage 2,4
How to perform Pagination in SQL Server by using OFFSET FETCH clause - TSQL Tutorial





1 comment:

  1. I blog on a popular social network and recently I thought about creating an exclusive site for my blog. Having studied many portfolios of web developers, I realized that I need help australian website development https://www.shtudio.com.au/services/web-development/ and I decided to turn to them. These guys listened to all my wishes, made competent adjustments and made a thematic site with a blog for me. I am very glad that I found this professional development team.

    ReplyDelete