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