SQL Server: Tips and Tricks - 2 (2017)

Filtering by OFFSET-FETCH Options in Select query

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) Lets 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

https://2.bp.blogspot.com/-0-nhDVpBOwQ/V2f7gQ4YzaI/AAAAAAAAfPM/1dDMVPfWzwYLkr5QIW1DCRPg0s-YVSGawCLcB/s640/Capture.PNG


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;

https://1.bp.blogspot.com/-nZQYcmBCNI0/V2f-E9z4gbI/AAAAAAAAfPY/erdptDJ06NUBUvAlnUwDglOp0w31_YukgCLcB/s640/Capture.PNG


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

https://3.bp.blogspot.com/--AEYoB69r8o/V2gBCGsnBBI/AAAAAAAAfPk/c2CDClN6y6wMD9ITz0yv5nbuoraHIUkxgCLcB/s640/Capture.PNG