title: Paging of records using sql server stored procedure
body: <p>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:</p>
  
  <pre><code>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
  </code></pre>