OFFSET FETCH NOT WORKING

Question

Hi, All

My query is like:

        select businessentityid,LoginID
from [HumanResources].[Employee]
order by BusinessEntityID desc
OFFSET 10 ROWS
    FETCH NEXT 30 ROWS ONLY

but it does not work. 

error message is :

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'OFFSET'.
Msg 153, Level 15, State 2, Line 7
Invalid usage of the option NEXT in the FETCH statement.

anyone has a clue ?

thank you in advance.

Answer 1

Hi Leading120

Is this the entire query? Looks to be part of a cursor...

---

Pérez

Answer 2

Are you on SQL Server 2012?

It works for me with 30, 100, 200, 5:

select businessentityid,LoginID
 from [HumanResources].[Employee]
 order by BusinessEntityID desc
 OFFSET 10 ROWS
     FETCH NEXT 200 ROWS ONLY
/*
businessentityid	LoginID
280	adventure-works\pamela0
279	adventure-works\tsvi0
278	adventure-works\garrett1
277	adventure-works\jillian0
276	adventure-works\linda3
*/

---

Kalman Toth SQL 2008 GRAND SLAM
New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 3

Try

select @@Version

If you're version is not SQL Server 2012, you can not use the above syntax.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

I copied and pasted your exact query and ran it in the AdventureWorks2012 database without an error. OFFSET FETCH was brought to T-SQL in SQL 2012 and will fail with this error in previous versions. Are you using running this on an SQL Server 2012 instance?