Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
sparrow37
Posting Yak Master
148 Posts |
Posted - 2013-03-28 : 04:35:22
|
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 = 1ASSELECT *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 |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-03-28 : 04:48:52
|
http://www.sqlteam.com/article/server-side-paging-using-sql-server-2005 KH[spoiler]Time is always against us[/spoiler] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-28 : 04:50:19
|
what will be values you pass for pagenumber, totalnumber of records, current page etc? whats the difference between pagenumber and current page?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|