寒号鸟

其实我也不怎么懂MSSQL的存储过程,可是我的网站数据量超过十万了,必须要用到分页过程,所以在网上找了一些过来硬拼,调式一下可以用了.

分两个过程,第一个用来获取记录集总数,第二个用来分页,本来第二个里就可以输出记录集总数,但听说用recordset方法不能获取,所以还是用了两个.

根据查询语句SQL进行分页

过程一.

CREATE procedure dbo.counts
(
@sqlstr nvarchar(500),--查询语句
@count int output,---输出值，记录集总数
@idstr char(100) --主键KEY
)
as
declare @strSQL nvarchar(4000)
set @strSQL = ' select @count=count('+@idstr+') from ('+@sqlstr+') as t '
exec sp_executesql
@strSQL,
N'@count int=0 OUTPUT',
@count=@count OUTPUT
GO

过程二.

CREATE PROCEDURE Pagination
(
 @SQL nvarchar(1024),   --查询语句
 @PageSize int = 20,    --分页大小
 @PageIndex int = 0,    --分页索引
 @Sort nvarchar(100) = '',    --排序字段
 @TotalCount int = 0 output --总数   
)
AS

set nocount on
/*声明查询字符串*/
declare @strSQL nvarchar(4000)

set @strSQL = ' select @TotalCount=count(*) from ('+@SQL+') as t '

/*取得查询结果总数*/
exec sp_executesql
@strSQL,
N'@TotalCount int=0 OUTPUT',
@TotalCount=@TotalCount OUTPUT

declare @ItemCount int
declare @_PageIndex int

set @_PageIndex = @PageIndex + 1;
/*确定搜索边界*/
set @ItemCount = @TotalCount - @PageSize * @_PageIndex

if(@ItemCount < 0)
    set @ItemCount = @ItemCount + @PageSize
else
    set @ItemCount = @PageSize

if(@ItemCount < 0) return 1

if(@Sort != '')
begin
    /*声明排序变量*/
    declare @IndexSort1 nvarchar(50), @IndexSort2 nvarchar(50), @Sort1 nvarchar(50), @Sort2 nvarchar(50)
   
    SET @Sort1 = @Sort
    SET @Sort2 = Replace(Replace(Replace(@Sort, 'DESC', '@SORT'), 'ASC', 'DESC'), '@SORT', 'ASC')

    set @strSQL = 'SELECT * FROM
    (SELECT TOP ' + STR(@ItemCount) + ' * FROM
    (SELECT TOP ' + STR(@PageSize * @_PageIndex) + ' * FROM
    ('+@SQL+') AS t0
    ORDER BY '+@Sort1 +') AS t1
    ORDER BY '+@Sort2 +') AS t2
    ORDER BY ' +@Sort
end
else
begin
    set @strSQL = 'SELECT * FROM
    (SELECT TOP ' + STR(@ItemCount) + ' * FROM
    (SELECT TOP ' + STR(@PageSize * @_PageIndex) + ' * FROM
    ('+@SQL+') As t0)
    aS t1)
    AS t2'
end

exec sp_executesql
@strSQL
GO

以下是用法

<%

SQL="select id from product where id < 10000"  '查询语句,自定义

idstr="id" '主键

Set MyComm=Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection=conn
MyComm.CommandText="counts"
MyComm.CommandType=4
MyComm.Prepared=true

MyComm.Parameters.append MyComm.CreateParameter( "@sqlstr",200,1,500,SQL)
MyComm.Parameters.append MyComm.CreateParameter( "@count",3,2)
MyComm.Parameters.append MyComm.CreateParameter( "@idstr",200,1,500,idstr)
MyComm.Execute
TotalRs=MyComm.Parameters( "@count").value
Set MyComm=Nothing
'response.write TotalRs&"<br>"

sql="exec Pagination SQL,20,0,'id desc'"   '20为每页记录数,0为第1页'id desc' 为排序
set rs=server.CreateObject("adodb.recordset")
rs.open sql,conn,1,1
do while not rs.eof
response.write rs("id")&"<br>"
rs.movenext
loop
%>