Alternate of offset in sql server

Question

I have below stored procedure in sql server 2016, its working fine there.

Now I need to create the same sp in sql 2008, now I am getting error :

Msg 102, Level 15, State 1, Procedure GetEmployees, Line 41 [Batch Start Line 0] Incorrect syntax near 'OFFSET'. Msg 153, Level 15, State 2, Procedure GetEmployees, Line 42 [Batch Start Line 0] Invalid usage of the option NEXT in the FETCH statement.

How to modify the same proc so that it can run over sql 2008 as well.

 --dbo.GetEmployees '',2,2  
    CreatePROCEDURE [dbo].GetEmployees    
    (    
     @SearchValue NVARCHAR(50) = '',    
     @PageNo INT = 0,    
     @PageSize INT = 10,    
     @SortColumn NVARCHAR(20) = 'Name',    
     @SortOrder NVARCHAR(20) = 'ASC'    
    )    
     AS BEGIN    
     SET NOCOUNT ON;    
     if @PageNo<0 set @PageNo=0   
     set @PageNo=@PageNo+1  
     SET @SearchValue = LTRIM(RTRIM(@SearchValue))    
     Set @SearchValue= nullif(@SearchValue,'')    
     ; WITH CTE_Results AS     
    (    
        SELECT EmployeeID, Name, City from tblEmployee     
     WHERE (@SearchValue IS NULL OR Name LIKE '%' + @SearchValue + '%')     
           ORDER BY    
      CASE WHEN (@SortColumn = 'EmployeeID' AND @SortOrder='ASC')    
                        THEN EmployeeID    
            END ASC,    
            CASE WHEN (@SortColumn = 'EmployeeID' AND @SortOrder='DESC')    
                       THEN EmployeeID    
           END DESC,    

         CASE WHEN (@SortColumn = 'Name' AND @SortOrder='ASC')    
                        THEN Name    
            END ASC,    
            CASE WHEN (@SortColumn = 'Name' AND @SortOrder='DESC')    
                       THEN Name    
      END DESC,    

      CASE WHEN (@SortColumn = 'City' AND @SortOrder='ASC')    
                        THEN City    
            END ASC,    
            CASE WHEN (@SortColumn = 'City' AND @SortOrder='DESC')    
                       THEN City    
      END DESC     
          OFFSET @PageSize * (@PageNo - 1) ROWS    
          FETCH NEXT @PageSize ROWS ONLY    
     ),    
    CTE_TotalRows AS     
    (    
     select count(EmployeeID) as MaxRows from tblEmployee WHERE (@SearchValue IS NULL OR Name LIKE '%' + @SearchValue + '%')    
    )    
       Select MaxRows TotalRecords, t.EmployeeID, t.Name, t.City,t.Department,t.Gender from dbo.tblEmployee as t, CTE_TotalRows     
       WHERE EXISTS (SELECT 1 FROM CTE_Results WHERE CTE_Results.EmployeeID = t.EmployeeID)    
       OPTION (RECOMPILE)    
       END 

Answer 1

You need a row_number() window function and in the OVER section you want to put your entire sorting expression. Note that I've created another CTE for readability, but you could get the same thing done with just a subquery.

Formatted code for the SELECT statement would be the following:

WITH CTE_Rownums AS (
  SELECT 
    EmployeeID, 
    Name, 
    City,
    row_number() over ( ORDER BY ... ) as rn -- put your entire order by here
  FROM tblEmployee     
  WHERE 
    @SearchValue IS NULL 
    OR Name LIKE '%' + @SearchValue + '%'
), CTE_Results AS (    
  SELECT EmployeeID, Name, City
  FROM CTE_Rownums
  WHERE 
    (rn > @PageSize * (@PageNo - 1)
    AND (rn <= @PageSize * @PageNo)
  ORDER BY rn   
 ), CTE_TotalRows AS (    
  SELECT count(EmployeeID) as MaxRows
  FROM tblEmployee 
  WHERE 
  @SearchValue IS NULL 
  OR Name LIKE '%' + @SearchValue + '%'
)
SELECT MaxRows TotalRecords, t.EmployeeID, t.Name, t.City,t.Department,t.Gender
FROM dbo.tblEmployee as t
CROSS JOIN CTE_TotalRows     
WHERE EXISTS (
  SELECT 1 
  FROM CTE_Results
  WHERE CTE_Results.EmployeeID = t.EmployeeID
)    
OPTION (RECOMPILE)

In the last SELECT I've replaced comma separated where clause with CROSS JOIN.