“在'OFFSET'附近的不正确语法”modift sql comm 2012到2008

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?

如何修改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

一个问题。如何为列出页面编写存储过程?这里是我的代码http://codepaste.net/gq5n6c

Answer: http://codepaste.net/jjrkqr

答:http://codepaste.net/jjrkqr

3 个解决方案

#1

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.

正如在评论中发现的，错误的原因是SQL Server 2008不支持它。您可以根据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.

问题在于您没有定义@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):-

试试这个(因为您没有提到什么是@page。我把它当作某个常数，或者你可以声明它，然后设置它的值):-

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

#2

For people using Entity Framework, particulary database first, this error can occur if you develop with SQL 2012 but deploy to an earlier version.

对于使用实体框架(尤其是数据库)的人来说，如果您使用SQL 2012开发，但是部署到较早的版本中，就会出现这种错误。

The problem will occur if you use Take...Skip functionality, as SQL 2012 has a new syntax for this. See:

如果你用Take……跳过功能，因为SQL 2012有一个新的语法。看到的:

http://erikej.blogspot.co.uk/2014/12/a-breaking-change-in-entity-framework.html

The fix is to edit your .edmx file and change the ProviderManifestToken value from 2012 to your database version, e.g. 2008.

修复方法是编辑.edmx文件，并将ProviderManifestToken值从2012年更改为数据库版本，例如2008年。

#3

I encountered this when using Entity Framework. I was developing on a machine with SQL Server 2012. But deployed on a machine with SQL Server 2008. Instead of doing a skip and take on the query, I did a ToList() on the query and did a skip/take on that ToList() in memory. Not ideal, but at least it will work.

我在使用实体框架时遇到了这个问题。我在一台使用SQL Server 2012开发的机器上进行开发。但部署在一台带有SQL Server 2008的机器上。我在查询中做了一个ToList()，并在内存中对ToList()做了一个跳转/获取。这并不理想，但至少会起作用。

#1