Geek Answers Handbook

Paging records using a sql server stored procedure

I have a stored procedure that returns the result of two tables using an external join and where are the conditions. He also has an article order. I want to add paging to it to return only the requested number of records. How can I do it? Do I need to specify pagenumber, totalnumber of records, current page, etc.? My stored procedure:

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
+4
source share
3 answers

Use the CTE and OVER () parameter

 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)

An example using the 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 entries 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)
+7
source

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
0
source

More articles:


All Articles