Discuz 教程：Sqlserver 2000 下收件箱翻页错误解决方案

信息来源:

http://nt.discuz.net/showtopic-81073.html

首先感谢网友 starmwh 的反馈

该问题可能在 3.0 --3.1 的当前发布版本中都存在

解决方案：

在 Sqlserver 查询分析器中执行如下脚本，更新 getpmlist 存储过程

IF OBJECT_ID('dnt_getpmlist','P') IS NOT NULL
DROP PROC dnt_getpmlist
GO

CREATE PROCEDURE dnt_getpmlist
@userid int,
@folder int,
@pagesize int,
@pageindex int,
@inttype int
AS
DECLARE @strSQL varchar(5000)

DECLARE @msgformORtoID varchar(10)
SET @msgformortoid='msgtoid'

IF @folder=1 OR @folder=2
BEGIN
SET @msgformortoid='msgfromid'
END

IF @pageindex = 1
BEGIN
IF (@inttype <> 1)
SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [pmid],[msgfrom],[msgfromid],[msgto],[msgtoid],[folder],[new],

[subject],[postdatetime],[message] FROM [dnt_pms] WHERE [' + @msgformortoid + ']=' +STR(@userid) + ' AND [folder]=' +STR(@folder) + ' ORDER

BY [pmid] DESC'
ELSE
SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [pmid],[msgfrom],[msgfromid],[msgto],[msgtoid],[folder],[new],

[subject],[postdatetime],[message] FROM [dnt_pms] WHERE [' + @msgformortoid + ']=' +STR(@userid) + ' AND [folder]=' +STR(@folder) + ' ORDER BY

[pmid] DESC'

END
ELSE
BEGIN
SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [pmid],[msgfrom],[msgfromid],[msgto],[msgtoid],[folder],[new],[subject],

[postdatetime],[message] FROM [dnt_pms] WHERE [pmid] < (SELECT MIN([pmid]) FROM (SELECT TOP ' + STR((@pageindex-1)*@pagesize) + ' [pmid] FROM

[dnt_pms] WHERE [' + @msgformortoid + ']=' +STR(@userid) + ' AND [folder]=' +STR(@folder) + ' ORDER BY [pmid] DESC) AS tblTmp) AND [' +

@msgformortoid + ']=' +STR(@userid) + ' AND [folder]=' + STR(@folder) + ' ORDER BY [pmid] DESC'
IF (@inttype <> 1)
BEGIN
SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [pmid],[msgfrom],[msgfromid],[msgto],[msgtoid],[folder],

[new],[subject],[postdatetime],[message] FROM [dnt_pms] WHERE [pmid] < (SELECT MIN([pmid]) FROM (SELECT TOP ' + STR((@pageindex-1)*@pagesize)

+ ' [pmid] FROM [dnt_pms] WHERE [' + @msgformortoid + ']=' +STR(@userid) + ' AND [folder]=' +STR(@folder) + ' ORDER BY [pmid] DESC) AS tblTmp)

AND [' + @msgformortoid + ']=' +STR(@userid) + ' AND [folder]=' +STR(@folder) + ' ORDER BY [pmid] DESC'
END
END

EXEC(@strSQL)
GO

给大家的使用造成的不变请谅解，感谢大家长久以来对我们的支持