这是我去年在理想国开发Bto和Jax时研发的分页技术,在大数据量(百万、千万级以上)下尤其适用。已经经过了测试。一年来,一直在做Bto和Jax,一直没有拿出来共享。
而网上所标榜的那些大数据量分页技术,尤其什么铁拳之类,有根本性的缺陷,虽然也能实现,但在排序、性能上有一定不合理。
存储过程代码:
CREATE PROCEDURE Uto_paging
@pTop int,@pField nvarchar(300),@pTable nvarchar(700),@pWhere nvarchar(650),@pWhere2 nvarchar(650),@pOrder nvarchar(100),@pID nvarchar(25),@pIDType nvarchar(1)
,@pStart nvarchar(300),@pEnd nvarchar(1000),@page_size int,@page_no int,@RecordCount int output
AS
Begin
Declare @sqlstr nvarchar(4000),@IDStr nvarchar(2200)
Declare @lower int,@upper int,@upper1 int
Set @lower=(@page_no-1)*@page_size
Set @upper=@lower+@page_size
Set @upper1=@upper+1
Set nocount on
Set @sqlstr=N'Set @i=0'+char(13)
Set @sqlstr=@sqlstr+N'Set @IDStr=N'''' '+char(13)
Set @sqlstr=@sqlstr+N'Select '
IF @pTop>0
Set @sqlstr=@sqlstr+N'Top '+Cast(@pTop As nvarchar(10))+N' '
Set @sqlstr=@sqlstr+N'@i=@i+1,@IDStr=Case when @i>'+Cast(@upper As nvarchar(10))+N' then @IDStr '
Set @sqlstr=@sqlstr+N'when @i>'+Cast(@lower As nvarchar(10))+N' and @i<'+Cast(@upper1 As nvarchar(10))+N' then @IDStr+'
IF @pIDType='1'
Set @sqlstr=@sqlstr+N'Cast('+@pID+N' As nvarchar(10))+'','' '
IF @pIDType='2'
Set @sqlstr=@sqlstr+@pID+N'+'','' '
Set @sqlstr=@sqlstr+N'else N'''' End '
Set @sqlstr=@sqlstr+N'From '+@pTable+N' '+@pWhere+N' '+@pOrder
EXECUTE sp_executesql @sqlstr,N'@i int output,@IDStr nvarchar(2200) output', @RecordCount output,@IDStr output
Set nocount off
IF Len(@IDStr)>0
Begin
Set @IDStr=Left(@IDStr,Len(@IDStr)-1)
IF @pIDType='2'
Set @IDStr=''''+Replace(@IDStr, ',', ''',''')+''''
Set @sqlstr='Select '+@pField+' From '+@pTable+' Where '+@pID+' in ('+@IDStr+')'
IF Len(@pWhere2)>0
Set @sqlstr=@sqlstr+' and '+@pWhere2
IF Len(@pStart)>0
Set @sqlstr=@pStart+@sqlstr
IF Len(@pEnd)>0
Set @sqlstr=@sqlstr+@pEnd
Else
Set @sqlstr=@sqlstr+' '+@pOrder
End
else
Begin
Set @sqlstr='Select '+@pField+' From '+@pTable+' Where Set @sqlstr=@sqlstr+'-1'
Else
Set @sqlstr=@sqlstr+''''''
End
Exec(@sqlstr)
End
GO
Asp调用代码:
set cmd=Server.CreateObject("ADODB.Command")
With cmd
.ActiveConnection=conn
.CommandType=4
.CommandText="Uto_paging"
.Parameters.Append(cmd.CreateParameter("@pTop",3,1,,sqls(0))) 'select top的条数,0时表示所有
.Parameters.Append(cmd.CreateParameter("@pField",200,1,300,sqls(1))) '字段
.Parameters.Append(cmd.CreateParameter("@pTable",200,1,700,sqls(2))) '表
.Parameters.Append(cmd.CreateParameter("@pWhere",200,1,650,sqls(3))) 'where
.Parameters.Append(cmd.CreateParameter("@pWhere2",200,1,650,sqls(4))) 'where2,二次查询筛选
.Parameters.Append(cmd.CreateParameter("@pOrder",200,1,100,sqls(5))) 'order等
.Parameters.Append(cmd.CreateParameter("@pID",200,1,25,sqls(6))) '主键
.Parameters.Append(cmd.CreateParameter("@pIDType",200,1,1,sqls(7))) '主键类型,1为int,2为char
.Parameters.Append(cmd.CreateParameter("@pStart",200,1,300,sqls(8))) '头部
.Parameters.Append(cmd.CreateParameter("@pEnd",200,1,1000,sqls(9))) '尾部
.Parameters.Append(cmd.CreateParameter("@page_size",3,1,,sqls(10))) '页大小
.Parameters.Append(cmd.CreateParameter("@page_no",3,1,,sqls(11))) '页码
.Parameters.Append(cmd.CreateParameter("@RecordCount",3,2)) '返回的记录数
.Execute()
getList=cmd.Parameters("@RecordCount")
set rs=cmd.Execute()
End With
Set cmd=nothing