利用SQL隐藏的存储过程分页

用SQL隐藏的存储过程分页

create procedure p_splitpage
@sql nvarchar(4000),--要执行的sql语句
@page int=1, --要显示的页码
@pageSize int=20,--每页的大小
@pageCount int=0 out,--总页数
@recordCount int=0 out--总记录数
as
set nocount on
declare @p1 int
exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
set @recordCount = @pageCount
select @pagecount=ceiling(1.0*@pagecount/@pagesize)
,@page=(@page-1)*@pagesize+1
exec sp_cursorfetch @p1,16,@page,@pagesize
exec sp_cursorclose @p1

GO

--------------------------------------------------------------------------------------

调用方法

set cmd = server.CreateObject("adodb.command")

'调用存储过程分页
cmd.ActiveConnection = m_conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "p_SplitPage"
cmd.Parameters.Append cmd.CreateParamete("@sql",adVarWChar,adParamInput,4000,sql_str) 'SQL语句
cmd.Parameters.Append cmd.CreateParameter("@page",adInteger,adParamInput,4,m_page) '页码
cmd.Parameters.Append cmd.CreateParameter("@pageSize",adInteger,adParamInput,4,m_pageSize) '每页大小
cmd.Parameters.Append cmd.CreateParameter("@pageCount",adInteger,adParamOutput,4,m_pageCount) '总页数
cmd.Parameters.Append cmd.CreateParameter("@recordCount",adInteger,adParamOutput,4,m_recordCount) '总记录数
set rs_temp = cmd.Execute

m_pageCount = cmd.Parameters("@pageCount").value
m_recordCount = cmd.Parameters("@recordCount").value

if m_pageCount = 0 then m_pageCount = 1

set rs_temp=nothing
set m_conn=nothing