28,391
社区成员
发帖
与我相关
我的任务
分享
''''通用分页函数,返回页号为Page的记录集''''''
'TableName-表名
'KeyId-排序关键字名
'FieldList-要查询的字段列表
'Cond-条件
'DESC-是否降序
'Page-当前页号
'PSize-页大小
'RCount-记录总数
'Ps -查询要用到的参数数组
FUNCTION RsPage(TableName, KeyID, FieldList, Cond, Desc, Page, PSize, RCount,Ps)
Dim boundID,Descs,MaxMinKeyID, GtLt,pcnt
if Desc then
Descs="ORDER BY " + KeyID +" DESC"
MaxMinKeyID = "MIN("+KeyID+")"
GtLt = "<"
else
Descs="ORDER BY " + KeyID
MaxMinKeyID = "MAX("+KeyID+")"
GtLt = ">"
end if
Sql = "SELECT COUNT("+KeyID+") FROM "+TableName+" WHERE "+ Cond
RCount = ExecInt(sql,Ps)
pcnt = (RCount-1) \ PSize + 1
if Page<1 then Page=1
if Page>Pcnt then page=Pcnt
sql = "SELECT "+MaxMinKeyID+" FROM (SELECT TOP "& (Page-1)*PSize & " " & KeyID &" FROM "+TableName+" WHERE ("+Cond+ ")"+DESCs + ") " & Left(KeyID,1)
boundID=ExecStr(sql,Ps)
if boundID = "" then GtLt="<>"
sql = "SELECT Top "&PSize & " " & FieldList & " FROM " & TableName &" WHERE (" & KeyID & GtLt &"'"& boundID & "') and (" & Cond & ")" & Descs
set RsPage=ExecRs(sql,Ps)
End Function
CREATE PROCEDURE [dbo].[pGetMesList]
@pagesize int,
@pageindex int
AS
DECLARE @strSQL varchar(5000)
--准备分页
declare @maxpage int --总页数
declare @datacount int --总数据条数
--set @maxpage=0
set @datacount=(select count(ID) from [pmessage])
if(@datacount%@pagesize)=0
begin
set @maxpage=(@datacount/@pagesize)
end
else
begin
set @maxpage=(@datacount/@pagesize)+1
end
--开始分页
IF @pageindex = 1
begin
SET @strSQL = 'select top '+str(@pagesize)+' '+str(@datacount)+' as datacount,'+str(@maxpage)+' as maxpage, * from [pmessage]
order by [ID] desc'
end
else
begin
SET @strSQL = 'select top '+str(@pagesize)+' '+str(@datacount)+' as datacount,'+str(@maxpage)+' as maxpage, * from [pmessage]
where ([ID] not in (select top '+str((@pageindex-1)*@pagesize)+' [ID] from [pmessage] order by [ID] desc))
order by [ID] desc'
end
EXEC(@strSQL)
GO