我的分页是这样写的,但愿能给你帮助.CREATE PROCEDURE Bbs_Article_List @iSubject NVARCHAR (255), @iPage INT, @iPageSize INT, @iPageCount INT OUTPUT, @iNewCount INT OUTPUT, @iReplyCount INT OUTPUT AS BEGIN SET NOCOUNT ON DECLARE @iStart DATETIME DECLARE @iEnd DATETIME DECLARE @iTmpSelect NVARCHAR (1000) DECLARE @iRowCount INT SELECT @iNewCount=Count(*) FROM tArticles WHERE rSubject=@iSubject And rIsDel=0 And rRepID=0 SELECT @iReplyCount=Count(*) FROM tArticles WHERE rSubject=@iSubject And rIsDel=0 And rRepID<>0 SELECT @iPageCount=Count(*) FROM tArticles WHERE rSubject=@iSubject And rIsTop=0 And rIsDel=0 And rRepID=0 SELECT @iPageCount=CEILING(@iPageCount/@iPageSize)+1IF @iPage<1 SELECT @iPage=1IF @iPage>@iPageCount SELECT @iPage=@iPageCountSET @iRowCount = (@iPage-1) * @iPageSize + 1SELECT @iTmpSelect = 'SET NOCOUNT ON;SET ROWCOUNT @SPIntRowCount;SELECT @SPIntBeginID = rLastReplyTime FROM tArticles WHERE rSubject='+@iSubject+' And rIsTop=0 And rIsDel=0 And rRepID=0 ORDER BY rLastReplyTime DESC' EXECUTE SP_EXECUTESQL @iTmpSelect, N'@SPIntRowCount INT,@SPIntBeginID DATETIME OUTPUT', @SPIntRowCount=@iRowCount,@SPIntBeginID=@iStart OUTPUTSET @iRowCount = @iPage * @iPageSizeSELECT @iTmpSelect = 'SET NOCOUNT ON;SET ROWCOUNT @SPIntRowCount;SELECT @SPIntEndID = rLastReplyTime FROM tArticles WHERE rSubject='+@iSubject+' And rIsTop=0 And rIsDel=0 And rRepID=0 ORDER BY rLastReplyTime DESC' EXECUTE SP_EXECUTESQL @iTmpSelect, N'@SPIntRowCount INT,@SPIntEndID DATETIME OUTPUT', @SPIntRowCount=@iRowCount,@SPIntEndID=@iEnd OUTPUTSELECT @iTmpSelect = 'SET NOCOUNT OFF;SET ROWCOUNT 0;SELECT * FROM tArticles WHERE rSubject='+@iSubject+' And rIsTop=0 And rIsDel=0 And rRepID=0 AND rLastReplyTime BETWEEN' IF @iEnd > @iStart SELECT @iTmpSelect = @iTmpSelect+' @SPIntBeginID AND @SPIntEndID ORDER BY rLastReplyTime DESC' ELSE SELECT @iTmpSelect = @iTmpSelect+' @SPIntEndID AND @SPIntBeginID ORDER BY rLastReplyTime DESC' EXECUTE SP_EXECUTESQL @iTmpSelect, N'@SPIntEndID DATETIME,@SPIntBeginID DATETIME', @SPIntEndID=@iEnd,@SPIntBeginID=@iStartSET NOCOUNT OFFEND GO
select top N * from table where ... order by SomeField
http://www.aspxcn.net
--------------------------
给你答案
没看到亚?
我想这应该是最优的了
@iSubject NVARCHAR (255),
@iPage INT,
@iPageSize INT,
@iPageCount INT OUTPUT,
@iNewCount INT OUTPUT,
@iReplyCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @iStart DATETIME
DECLARE @iEnd DATETIME
DECLARE @iTmpSelect NVARCHAR (1000)
DECLARE @iRowCount INT
SELECT @iNewCount=Count(*)
FROM tArticles
WHERE rSubject=@iSubject And rIsDel=0 And rRepID=0
SELECT @iReplyCount=Count(*)
FROM tArticles
WHERE rSubject=@iSubject And rIsDel=0 And rRepID<>0
SELECT @iPageCount=Count(*)
FROM tArticles
WHERE rSubject=@iSubject And rIsTop=0 And rIsDel=0 And rRepID=0
SELECT @iPageCount=CEILING(@iPageCount/@iPageSize)+1IF @iPage<1
SELECT @iPage=1IF @iPage>@iPageCount
SELECT @iPage=@iPageCountSET @iRowCount = (@iPage-1) * @iPageSize + 1SELECT @iTmpSelect = 'SET NOCOUNT ON;SET ROWCOUNT @SPIntRowCount;SELECT @SPIntBeginID = rLastReplyTime FROM tArticles WHERE rSubject='+@iSubject+' And rIsTop=0 And rIsDel=0 And rRepID=0 ORDER BY rLastReplyTime DESC'
EXECUTE SP_EXECUTESQL
@iTmpSelect,
N'@SPIntRowCount INT,@SPIntBeginID DATETIME OUTPUT',
@SPIntRowCount=@iRowCount,@SPIntBeginID=@iStart OUTPUTSET @iRowCount = @iPage * @iPageSizeSELECT @iTmpSelect = 'SET NOCOUNT ON;SET ROWCOUNT @SPIntRowCount;SELECT @SPIntEndID = rLastReplyTime FROM tArticles WHERE rSubject='+@iSubject+' And rIsTop=0 And rIsDel=0 And rRepID=0 ORDER BY rLastReplyTime DESC'
EXECUTE SP_EXECUTESQL
@iTmpSelect,
N'@SPIntRowCount INT,@SPIntEndID DATETIME OUTPUT',
@SPIntRowCount=@iRowCount,@SPIntEndID=@iEnd OUTPUTSELECT @iTmpSelect = 'SET NOCOUNT OFF;SET ROWCOUNT 0;SELECT * FROM tArticles WHERE rSubject='+@iSubject+' And rIsTop=0 And rIsDel=0 And rRepID=0 AND rLastReplyTime BETWEEN'
IF @iEnd > @iStart
SELECT @iTmpSelect = @iTmpSelect+' @SPIntBeginID AND @SPIntEndID ORDER BY rLastReplyTime DESC'
ELSE
SELECT @iTmpSelect = @iTmpSelect+' @SPIntEndID AND @SPIntBeginID ORDER BY rLastReplyTime DESC' EXECUTE SP_EXECUTESQL
@iTmpSelect,
N'@SPIntEndID DATETIME,@SPIntBeginID DATETIME',
@SPIntEndID=@iEnd,@SPIntBeginID=@iStartSET NOCOUNT OFFEND
GO
你的方法还是ms标准的dg分页方法最好的是select top