分页存储过程2-优快云博客

本文介绍了一个使用SQL实现的通用分页查询存储过程，该过程通过动态SQL语句实现，支持自定义表名、主键、排序字段及条件等参数。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

*************************************************************

*****************************************************
CREATE procedure CommonPage
@strTableName varchar(255),--从哪表中进行选择
@strPK varchar(255),--定义主键
@strSort varchar(255),--定义排序字段
@intSortFlag int,--排序标志
@strCondition varchar(500),--定义条件
@intPageSize int,--定义每页记录数
@intPageNo int--定义当前页数
As
declare @sSql varchar(1000)
declare @sSort varchar(255)
--根据传入参数不同进行升降序排列
if @intSortFlag=1
set @sSort=' desc'
else
set @sSort=' asc'
--生成SQL语句
set @sSql='select ccc.* from
(
select top '+cast(@intPageNo*@intPageSize as varchar(20))+' * from '+@strTableName+' '+@strCondition+' order by '+@strSort+' '+@sSort+','+@strPK+' '+@sSort+'
) as ccc
where ccc.'+@strPK+' not in
(
select top '+cast((@intPageNo-1)*@intPageSize as varchar(20))+' aaa.'+@strPK+' as '+@strPK+' from
(
select top '+cast(@intPageNo*@intPageSize as varchar(20))+' '+@strPK+' as '+@strPK+' from '+@strTableName+' '+@strCondition+' order by '+@strSort+' '+@sSort+','+@strPK+' '+@sSort+'
)as aaa
)'
--执行SQL语句
exec (@sSql)
--set @sSql=@strTableName+','+@strPK+','+@strSort+','+cast(@intSortFlag as varchar(20))+','+@strCondition+','+cast(@intPageSize as varchar(20))+','+cast(@intPageNo as varchar(20))
--print @sSql
GO

****************************************************
***************************************************

Set oComm=Server.CreateObject("ADODB.Command")
oComm.ActiveConnection=CONN
oComm.CommandType=4
oComm.CommandText="zhypmq.CommonPage"
oComm.Prepared=true
oComm.Parameters.Append oComm.CreateParameter("@p1",200,1,255,strTableName)
oComm.Parameters.Append oComm.CreateParameter("@p2",200,1,255,strPK)
oComm.Parameters.Append oComm.CreateParameter("@p3",200,1,255,strSort)
oComm.Parameters.Append oComm.CreateParameter("@p4",3,1,4,intSortFlag)
oComm.Parameters.Append oComm.CreateParameter("@p5",200,1,500,strCondition)
oComm.Parameters.Append oComm.CreateParameter("@p6",3,1,4,intPageSize)
oComm.Parameters.Append oComm.CreateParameter("@p7",3,1,4,intPageNo)
'执行存储过程得到结果集
Set rs1 = oComm.Execute