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)
