帮我看看这个Northwind的Orders表的分页怎么错了

[jjstar]

不知道是不是TOP用的不对啊？


USE Northwind

DECLARE @pageIndex int
DECLARE @pageSize int

SELECT @pageIndex = 1
SELECT @pageSize = 10

SELECT DISTINCT TOP @pageIndex OrderID, ShipCity, ShipRegion
FROM Orders
WHERE OrderID NOT IN
(SELECT TOP @pageSize * @pageIndex OrderID FROM Orders ORDER BY OrderID)
ORDER BY OrderID

/*
服务器: 消息 170，级别 15，状态 1，行 9
第 9 行: '@pageIndex' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，行 12
第 12 行: '@pageSize' 附近有语法错误。
*/

[txlicenhe]

SELECT TOP @pageSize * @pageIndex ...

这种只能用动态SQL语句来行。

[txlicenhe]

http://expert.csdn.net/Expert/topic/2365/2365596.xml?temp=.5068781
交流--查询第X页,每页Y条记录
邹建

[zarge]

USE Northwind

DECLARE @str varchar(8000)
DECLARE @pageIndex int
DECLARE @pageSize int

SELECT @pageIndex = 1
SELECT @pageSize = 10

SET @str = 'SELECT DISTINCT TOP ' + CAST(@pageIndex as varchar) + ' OrderID, ShipCity, ShipRegion
FROM Orders
WHERE OrderID NOT IN
(SELECT TOP ' + Cast(@pageSize * @pageIndex as varchar) + ' OrderID FROM Orders ORDER BY OrderID)
ORDER BY OrderID'

EXEC (@str)

[txlicenhe]

dim @str nvarchar(2000)
set @str = 'SELECT DISTINCT TOP '+cast(@pageIndex as varchar) +' OrderID, ShipCity, ShipRegion
FROM Orders
WHERE OrderID NOT IN
(SELECT TOP '+ cast(@pageSize as varchar)+' * '+ cast(@pageIndex as varchar) + ' OrderID FROM Orders ORDER BY OrderID)
ORDER BY OrderID '
exec(@str)

[shuiniu]

(SELECT TOP @pageSize * @pageIndex OrderID FROM Orders ORDER BY OrderID)
ORDER BY OrderID
----
top 后面不能跟变量，需要动态执行！！

[wzh1215]

动态执行！