Incorrect syntax error in SQL query

Question

DECLARE @startRowIndex int  
  set @startRowIndex=0
  Declare @maximumRows int 
  set @maximumRows=5

SET ROWCOUNT 5;

WITH OrderedEmployees As

SELECT *, ROW_NUMBER() OVER (Order By EmployeeID ASC) as RowNum FROM Employees 

 SELECT * FROM OrderedEmployees Where RowNum > @startRowIndex
 Order By EmployeeID ASC

I got this query from a website but when I run it in SQL Server Management Studio, it gives me the following error please help me out, I have intuition that its just a syntax mistake that is stumping me here , please help me out

Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'SELECT'

Lamak · Accepted Answer · 2012-05-30 20:59:30Z

3

I believe that this is what you want:

DECLARE @startRowIndex int  
  set @startRowIndex=0
  Declare @maximumRows int 
  set @maximumRows=5

SET ROWCOUNT 5

;WITH OrderedEmployees As
(
    SELECT *, ROW_NUMBER() OVER (Order By EmployeeID ASC) as RowNum 
    FROM Employees 
)
SELECT * 
FROM OrderedEmployees 
Where RowNum > @startRowIndex
Order By EmployeeID ASC

Though, I don't really know why you are choosing to use ROWCOUNT instead of just filtering your CTE. I would use:

DECLARE @startRowIndex int  
  set @startRowIndex=0
  Declare @maximumRows int 
  set @maximumRows=5

;WITH OrderedEmployees As
(
    SELECT *, ROW_NUMBER() OVER (Order By EmployeeID ASC) as RowNum 
    FROM Employees 
)
SELECT * 
FROM OrderedEmployees 
Where RowNum > @startRowIndex AND RowNum <= @maximumRows
Order By EmployeeID ASC

answered May 30, 2012 at 20:59

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

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

Collectives™ on Stack Overflow

Incorrect syntax error in SQL query

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