1

I have this razor statement

sql = "SELECT * FROM CarBike" +
       "Order By id OFFSET @0 ROWS FETCH NEXT @1 ROWS ;";

var result = db.Query(sql, offset, pageSize);

i am getting error

Incorrect syntax near the keyword 'By'.
Invalid usage of the option NEXT in the FETCH statement.

System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'By'.
Invalid usage of the option NEXT in the FETCH statement.

Please help me to correct this error

podiluska
  • 50,950
  • 7
  • 98
  • 104
ktm
  • 6,025
  • 24
  • 69
  • 95

1 Answers1

3

You need a space between CarBike and Order by

sql = "SELECT * FROM CarBike" + 
   " Order By id OFFSET @0 ROWS FETCH NEXT @1 ROWS ;";

NB: OFFSET/FETCH is SQL 2012+ only.

To achieve similar results in previous versions

select * from
(
select *, ROW_NUMBER() over (order by id) rn
from CarBike
) v
where rn between @0+1 and @0+@1
order by id
podiluska
  • 50,950
  • 7
  • 98
  • 104
  • now i ma getting Incorrect syntax near 'OFFSET'. Invalid usage of the option NEXT in the FETCH statement. – ktm Sep 13 '12 at 09:04
  • i am using sql 2008 now i ma getting Incorrect syntax near 'OFFSET'. Invalid usage of the option NEXT in the FETCH statement. – ktm Sep 13 '12 at 09:06
  • 1
    If you're using SQL2008, you can't use the SQL 2012 Offset/Fetch syntax. http://msdn.microsoft.com/en-us/library/ms188385(v=sql.100).aspx – podiluska Sep 13 '12 at 09:06
  • could you pls give me alternative syntax for sql 2008 – ktm Sep 13 '12 at 09:11
  • http://stackoverflow.com/questions/2244322/how-to-do-pagination-in-sql-server-2008 – Germann Arlington Sep 13 '12 at 09:16