I'm listing questions with this
SELECT q.qTitle, q.qDescription, q.qCreatedOn, u.uCode, u.uFullname, qcat.qcatTitle, q.qId, q.qStatus
FROM tblQuestion AS q INNER JOIN tblUser AS u
ON q.uId = u.uId INNER JOIN tblQuestionCategory AS qcat
ON q.qcatId = qcat.qcatId
WHERE (q.qStatus = 1)
ORDER BY q.qCreatedOn DESC
OFFSET @page*10 ROWS FETCH NEXT 10 ROWS ONLY
But there is a problem in my server,
Incorrect syntax near 'OFFSET'.
Invalid usage of the option NEXT in the FETCH statement.
How can I modify my query for sql server 2008?
One more question. How can I write a stored procedure for listing pages? Here is my full of code http://codepaste.net/gq5n6c
Answer: http://codepaste.net/jjrkqr
As found out in the comments the reason for the error is because of the fact that SQL Server 2008 does not support it. You may try to change the query according to SQL Server 2012.
Something like this:-
SELECT column1
FROM (
SELECT column1, ROW_NUMBER() OVER (ORDER BY column_id) AS x
FROM mytable
) AS tbl
WHERE tbl.x BETWEEN 20 AND 30
In your code:-
SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY q.qId) AS rownumber
FROM tblQuestion AS q
INNER JOIN tblUser AS u ON q.uId = u.uId
INNER JOIN tblQuestionCategory AS qcat ON q.qcatId = qcat.qcatId ) as somex
WHERE somex.rownumber BETWEEN 11 AND 20
The issue is because you have not defined @page.
Try this (As you have not mentioned what is @page. I am taking it as some constant or may be you can declare it and then set the value for it):-
declare @page int
set @page = 5 // You may set any value here.
SELECT q.qTitle, q.qDescription, q.qCreatedOn, u.uCode,
u.uFullname, qcat.qcatTitle, q.qId, q.qStatus
FROM tblQuestion AS q
INNER JOIN tblUser AS u ON q.uId = u.uId
INNER JOIN tblQuestionCategory AS qcat ON q.qcatId = qcat.qcatId
WHERE (q.qStatus = 1)
ORDER BY q.qCreatedOn DESC
OFFSET (@page*10) ROWS
FETCH NEXT 10 ROWS ONLY