Error While OFFSET FETCH | SQL SERVER

Question

I am trying to do pagination in SQL SERVER and I am getting two errors on OFFSET and ROWS

CREATE PROCEDURE XYZ
@offset int,
@limit int, 
@order char(4),
@Id int
AS
BEGIN
SELECT * FROM TABLE
WHERE ID = @id
ORDER BY 
CASE WHEN @order = 'desc' THEN [TIME] END DESC,
CASE WHEN @order = 'asc' THEN [TIME] END ASC
*OFFSET* @offset FETCH ROWS NEXT @limit *ROWS* ONLY
END
GO

Error on OFFSET:

Incorrect Syntax near 'OFFSET'.

Error on ROWS (Second one):

Incorrect Syntax near 'ROWS'. Expecting FROM.

Can someone help me out with this ?

*OFFSET* @offset FETCH ROWS NEXT @limit *ROWS* ONLY should be OFFSET @offset ROWS FETCH NEXT @limit ROWS ONLY — Zohar Peled
– Zohar Peled, Commented Sep 5, 2017 at 8:41
@ZoharPeled I have used asterisk to identify the two keywords on which I am getting error. — siddharth
– siddharth, Commented Sep 5, 2017 at 8:44
@Damien_The_Unbeliever Okay, I think where the problem is. I am using SQL Server 2008. :| — siddharth
– siddharth, Commented Sep 5, 2017 at 8:47
@siddharth Yes, but my comment is referring to the fact that FETCH ROWS should be ROWS FETCH... — Zohar Peled
– Zohar Peled, Commented Sep 5, 2017 at 8:57

Zohar Peled · Accepted Answer · 2017-09-06 06:23:33Z

1

Well, in SQL Server 2008 you obviously can't use the fetch...next clause that was introduced in 2012.
However, this doesn't mean you can't do pagination.
One simple way is to use a cte with row_number:

;WITH CTE AS
(
    SELECT  *,  -- Don't be lazy, specify the Columns list...
            ROW_NUMBER() OVER
            (
                ORDER BY 
                CASE WHEN @order = 'desc' THEN [TIME] END DESC,
                CASE WHEN @order = 'asc' THEN [TIME] END ASC
            ) As rn 
    FROM TABLE
    WHERE ID = @id
)

SELECT * -- Don't be lazy, specify the Columns list...
FROM CTE
WHERE rn >= @offset
AND rn <= @offset + @limit

edited Sep 6, 2017 at 6:23

answered Sep 5, 2017 at 9:07

Zohar Peled

82.9k11 gold badges80 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error While OFFSET FETCH | SQL SERVER

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related