To paginate a select query normally I would do this:
SELECT * FROM items WHERE condition = 1 LIMIT $start, $items
However, in MSSQL there is no LIMIT condition, so I tried with:
SELECT * FROM items WHERE condition = 1 OFFSET $start ROWS FETCH NEXT $items ROWS ONLY
And I get the error:
42000 - [SQL Server]Invalid usage of the option NEXT in the FETCH statement.
So what exactly should I do to paginate results?
The MS SQL equivalent to MySQL's LIMIT clause is TOP.
SELECT TOP 10 * FROM items WHERE condition = 1;
Will return the top ten rows, the same way it is done with LIMIT here:
SELECT * FROM items WHERE condition = 1 stuff LIMIT 10;
For more details (since I don't know all the context of your code) you can take a look here.
This may help you. The OFFSET-FETCH clause provides you with an option to fetch only a window or page of results from the result set. OFFSET-FETCH can be used only with the ORDER BY clause.
SELECT P_Name FROM ITEMS ORDER BY P_Name OFFSET 10 ROWS;
SRC: https://technet.microsoft.com/en-us/library/gg699618.aspx