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
Best Answer
Use a Common Table Expression (CTE):