7

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
12

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
  • Brilliant, Simply Brilliant!!! Exactly what I am looking for Thank you! – Mark Kram Sep 12 '17 at 3:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.