Paging of records using sql server stored procedure

Question

I have a stored procedure which returns result from two tables using outer join and where conditions. It has order by clause as well. I want to add paging to it so that only requested number of records are returned. How can I do it? I need to supply pagenumber, totalnumber of records, current page etc ? My stored procedure is:

CREATE PROCEDURE [dbo].[hr_SearchVacanciesForService]

    @SearchText NVARCHAR(50) = NULL,
    @DutyStationID INT = NULL,
    @VacancyCategoryIDs VARCHAR(1000) = NULL,
    @Language INT = 1
AS

SELECT *
FROM dbo.hr_Vacancies LEFT OUTER JOIN dbo.hr_DutyStations ON dbo.hr_Vacancies.DutyStationID = dbo.hr_DutyStations.DutyStationID 
    LEFT OUTER JOIN dbo.hr_Companies    
        ON dbo.hr_Vacancies.CompanyID = dbo.hr_Companies.CompanyID 
WHERE dbo.hr_Vacancies.Deleted = 0 
        AND (dbo.hr_Vacancies.JobTitleLang1 LIKE @LoacalSeacrchText 
        OR dbo.hr_Vacancies.JobTitleLang2 LIKE @LoacalSeacrchText 
        OR dbo.hr_Vacancies.DescriptionLang1 LIKE @LoacalSeacrchText 
        OR dbo.hr_Vacancies.DescriptionLang2 LIKE @LoacalSeacrchText    
    AND (dbo.hr_Vacancies.DutyStationID = @DutyStationID OR @DutyStationID IS NULL OR @DutyStationID = 0)
    ORDER BY HavePriority DESC, StartDate DESC, dbo.hr_Vacancies.VacancyID DESC

Solution 1

Use option with CTE and OVER() clause

CREATE PROCEDURE [dbo].[hr_SearchVacanciesForService]
@SearchText NVARCHAR(50) = NULL,
@DutyStationID INT = NULL,
@VacancyCategoryIDs VARCHAR(1000) = NULL,
@Language INT = 1,
@NumberOfPages int
AS
;WITH cte AS
 (
  SELECT *, ROW_NUMBER() OVER (ORDER BY HavePriority DESC, StartDate DESC, dbo.hr_Vacancies.VacancyID DESC) AS Pages
  FROM dbo.hr_Vacancies LEFT OUTER JOIN dbo.hr_DutyStations ON dbo.hr_Vacancies.DutyStationID = dbo.hr_DutyStations.DutyStationID 
                        LEFT OUTER JOIN dbo.hr_Companies ON dbo.hr_Vacancies.CompanyID = dbo.hr_Companies.CompanyID 
  WHERE dbo.hr_Vacancies.Deleted = 0 
    AND (dbo.hr_Vacancies.JobTitleLang1 LIKE @LoacalSeacrchText 
    OR dbo.hr_Vacancies.JobTitleLang2 LIKE @LoacalSeacrchText 
    OR dbo.hr_Vacancies.DescriptionLang1 LIKE @LoacalSeacrchText 
    OR dbo.hr_Vacancies.DescriptionLang2 LIKE @LoacalSeacrchText    
    AND (dbo.hr_Vacancies.DutyStationID = @DutyStationID OR @DutyStationID IS NULL OR @DutyStationID = 0)
  )
  SELECT *, COUNT(*) OVER() AS totalOfPages
  FROM cte
  WHERE Pages BETWEEN 1 AND ISNULL(@NumberOfPages, Pages)

Example using OVER() clause with expressions:

SELECT ... ROW_NUMBER() OVER (ORDER BY
CASE WHEN dbo.hr_Vacancies.Priority = 0 
     THEN 0 ELSE 
CASE WHEN CONVERT(DATETIME,CONVERT(CHAR(10),dbo.hr_Vacancies.PriorityDateExpired,101)) > CONVERT(DATETIME,CONVERT(CHAR(10),GETDATE(),101)) OR dbo.hr_Vacancies.PriorityDateExpired IS NULL 
     THEN 1 ELSE 0 END END DESC, your_second_expression_StartDate DESC) 

If you want to show records from 20 to 30:

CREATE PROCEDURE [dbo].[hr_SearchVacanciesForService]

    @SearchText NVARCHAR(50) = NULL,
    @DutyStationID INT = NULL,
    @VacancyCategoryIDs VARCHAR(1000) = NULL,
    @Language INT = 1,
    @StartPage int = NULL,
    @EndPage int = NULL
AS
;WITH cte AS
 (
  SELECT *, ROW_NUMBER() OVER (ORDER BY your_case_expressionForColumnHavePriority DESC, your_case_expressionForColumnStartDate DESC, dbo.hr_Vacancies.VacancyID DESC) AS Pages
  FROM dbo.hr_Vacancies LEFT OUTER JOIN dbo.hr_DutyStations ON dbo.hr_Vacancies.DutyStationID = dbo.hr_DutyStations.DutyStationID 
                        LEFT OUTER JOIN dbo.hr_Companies ON dbo.hr_Vacancies.CompanyID = dbo.hr_Companies.CompanyID 
  WHERE dbo.hr_Vacancies.Deleted = 0 
          AND (dbo.hr_Vacancies.JobTitleLang1 LIKE @LoacalSeacrchText 
          OR dbo.hr_Vacancies.JobTitleLang2 LIKE @LoacalSeacrchText 
          OR dbo.hr_Vacancies.DescriptionLang1 LIKE @LoacalSeacrchText 
          OR dbo.hr_Vacancies.DescriptionLang2 LIKE @LoacalSeacrchText    
      AND (dbo.hr_Vacancies.DutyStationID = @DutyStationID OR @DutyStationID IS NULL OR @DutyStationID = 0)
  )
  SELECT *, COUNT(*) OVER() AS totalOfPages
  FROM cte
  WHERE Pages BETWEEN ISNULL(@StartPage, Pages) AND ISNULL(@EndPage, Pages)

Solution 2

You can read the following papers :

SQL Server stored procedures to page large tables or queries

Or

Stored Procedure having Sorting, Paging and Filtering

Solution 3

OLD METHOD - Simple Pagination script with (limit,order direction,order column,start index)

@orderColumn  int ,
@orderDir  varchar(20),
@start  int ,
@limit  int,
@searchKey varchar(20)

declare @to as int = @start+@limit

select IDENTITY(int, 1, 1) AS SnoID,null as abc, make.link,make.manf,make.name
into #tempMake
 from make where status=1 and UPPER(make.name) like upper('%'+@searchKey+'%')


select * from #tempMake where SnoID>@start and SnoID<=@to
    order by               
                CASE WHEN @orderColumn = 1 AND @orderdir = 'desc' THEN #tempMake.[manf] END DESC,
                CASE WHEN @orderColumn = 1 AND @orderdir = 'asc' THEN #tempMake.[manf] END ASC,
                CASE WHEN @orderColumn = 2 AND @orderdir = 'desc' THEN #tempMake.[link] END DESC,
                CASE WHEN @orderColumn = 2 AND @orderdir = 'asc' THEN #tempMake.[link] END ASC

select count(#tempMake.SnoID) as row_count from #tempMake 

drop table #tempMake

Comments

[DotnetSparrow]

Thanks for your response. What If I need to select few columns and not * like 15 columns, Do I need to repeat then in both selects ? inside with and after with ?

[Aleksandr Fedorenko]

No only in first SELECT statement.

[DotnetSparrow]

It gives error on havepriority : ;WITH cte AS ( SELECT ROW_NUMBER() OVER (ORDER BY HavePriority DESC, StartDate DESC, dbo.hr_Vacancies.VacancyID DESC) AS Pages, ... error is Msg 207, Level 16, State 1, Procedure hr_SearchVacanciesForService, Line 33 Invalid column name 'HavePriority'.. where as this column exists

[Aleksandr Fedorenko]

Column HavePriority and StartDate exists in your tables?

[Aleksandr Fedorenko]

You can check columns name again, use name in form schema.object_name.column_name or use table alias

[DotnetSparrow]

I see it exists like this: CASE WHEN dbo.hr_Vacancies.Priority = 0 THEN 0 ELSE CASE WHEN CONVERT(DATETIME,CONVERT(CHAR(10),dbo.hr_Vacancies.PriorityD‌​ateExpired,101)) > CONVERT(DATETIME,CONVERT(CHAR(10),GETDATE(),101)) OR dbo.hr_Vacancies.PriorityDateExpired IS NULL THEN 1 ELSE 0 END END AS HavePriority,

[DotnetSparrow]

Also, If I want to show records from 20 to 30, how can I do with this stored procedure ?