OFFSET FETCH in SQL Server 2008

Question

I have this query to paginate the results and it was working fine on SQL Server 2012. However I had to move my database to SQL Server 2008 and now my stored procedure is not working. I did some research and got to know that OFFSET does not work in SQL Server 2008. What alternative should I use now? How i achieve the same functionality with SQL Server 2008?

Here is my stored procedure:

CREATE PROCEDURE [dbo].[sp_JobSearch]
 @EnteredKeyword nvarchar(200) = '', 
 @EnteredLocation nvarchar(200) = '',
 @PageNumber INT = 1,
 @PageSize INT = 40
AS
BEGIN
 SELECT 
 MasterJob.Title, MasterJob.CompanyName, 
 MasterJob.ShortDesc, MasterJob.Url,MasterJob.PostedTime, 
 MasterJob.Location, JobBoard.JobBoardName
 FROM 
 MasterJob 
 LEFT JOIN 
 JobBoard ON MasterJob.JobBoardId = JobBoard.JobBoardId
 WHERE 
 (MasterJob.Title LIKE '%' + @EnteredKeyword + '%')
 AND (MasterJob.Location LIKE '%' + @EnteredLocation + '%')
 ORDER BY 
 [MasterJobId] 
 OFFSET @PageSize * (@PageNumber - 1) ROWS
 FETCH NEXT @PageSize ROWS ONLY;
END

• Aaron Bertrand: sqlservercentral.com/articles/T-SQL/66030

Answer 1

Use a Common Table Expression (CTE):

 CREATE PROCEDURE [dbo].[sp_JobSearch]
 @EnteredKeyword nvarchar(200) = '', 
 @EnteredLocation nvarchar(200) = '',
 @PageNumber INT = 1,
 @PageSize INT = 40
 AS
 BEGIN
 WITH CTE AS
 (
 SELECT 
 ROW_NUMBER() OVER ( ORDER BY [MasterJobId] ) AS RowNum ,
 MasterJob.Title, MasterJob.CompanyName, MasterJob.ShortDesc, 
 MasterJob.Url,MasterJob.PostedTime, MasterJob.Location, JobBoard.JobBoardName 
 FROM MasterJob 
 LEFT JOIN JobBoard ON MasterJob.JobBoardId = JobBoard.JobBoardId
 WHERE 
 (MasterJob.Title LIKE '%' + @EnteredKeyword + '%')
 AND( MasterJob.Location LIKE '%' + @EnteredLocation + '%' )
 )
 SELECT 
 Title, CompanyName, ShortDesc, Url, PostedTime, Location, JobBoardName
 FROM CTE 
 WHERE 
 (RowNum > @PageSize * (@PageNumber - 1) )
 AND 
 (RowNum <= @PageSize * @PageNumber )
 Order By RowNum 
 END

• Mark Kram: Brilliant, Simply Brilliant!!! Exactly what I am looking for Thank you!