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

`OFFSET` is not available in `SQL Server 2008`, use `row_number()` instead and calculate the row number using `@PageSize` that you have — Squirrel, Jun 28 '18 at 06:24
Possible duplicate of [Row Offset in SQL Server](https://stackoverflow.com/questions/187998/row-offset-in-sql-server) — EzLo, Jun 28 '18 at 06:33

score 2 · Accepted Answer · answered Jun 28 '18 at 06:31

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.

getting error Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. and Incorrect syntax near ','. — Sunil Kumar, Jun 28 '18 at 06:38
Add `;` before `WITH`, you probably copied my solution and you don't have it now. — Kamil Gosciminski, Jun 28 '18 at 06:42

Vedad Herceglija · Answer 2 · 2021-08-04T14:56:17.933

1

If you use 2008 R2 or older you can't use OFFSET FETCH,

you have alternative to use ROW_NUMBER() and rewrite your query for examle

with OFFSET

SELECT Price
FROM dbo.Inventory
ORDER BY Price OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY

this query without OFFSET using ROW_NUMBER()

SELECT Price
FROM
(
SELECT Price
ROW_NUMBER() OVER (ORDER BY Price) AS Seq
FROM dbo.Inventory
)t
WHERE Seq BETWEEN 11 AND 15

edited Aug 04 '21 at 14:56

answered Aug 04 '21 at 14:43

Vedad Herceglija

53
4

Alternate of offset in sql server

2 Answers2