SQL 通用分页存储过程。

Question

本帖最后由 terry2018 于 2011-09-22 17:34:07 编辑

Answer 1

ALTER PROCEDURE [dbo].[Basic_Pagination2005]
@tblName      nvarchar(200),     --表名
@fidlelist    nvarchar(1000),   --要查询字段
@fldName      nvarchar(100),    --排序字段
@PageSize     int,              --页尺寸
@PageIndex    int,              --页码
@IsReCount    bit ,             -- 返回记录总数, 非 0 值则返回
@OrderType    bit,              -- 设置排序类型, 非 0 值则降序
@strWhere nvarchar(1000)        --查询条件
AS
declare @sqlstr nvarchar(4000),
@tmpwhere nvarchar(4000),@tmporder nvarchar(100)
BEGIN
if @OrderType != 0
begin
set @tmporder = @fldName +' desc '
end
else
begin
        set @tmporder = @fldName +' asc '
end
set @tmpwhere='';
if(@strWhere!='')
begin
set @tmpwhere=' where '+@strWhere;
end
set @sqlstr=N'select * from
(select  '+@fidlelist+', ROW_NUMBER() OVER(orderby '+@tmporder+') as row from '+@tblName+@tmpwhere+') 
tmp where row between '+cast(((@PageIndex-1)*@PageSize+1) as nvarchar)+' and '+cast(@PageIndex*@PageSize as nvarchar);  
exec sp_executesql @sqlstr
if @IsReCount != 0
begin
set @sqlstr=N'select count(*) as Total from '+ @tblName+@tmpwhere
exec sp_executesql @sqlstr    
end
END

Answer 2

create proc [dbo].[Pagination] 
@pageSize int,--每页的记录条数
@pageIndex int,--当前第几页
@tableName nvarchar(100), --要操作的表名或视图
@sortItem nvarchar(100), --用来排序的字段
@outItem nvarchar(100), --输出的字段项（列）
@sortType nvarchar(10), --用来排序的方法，仅限desc与asc
@sortWhere nvarchar(500) --用于分页查询的条件
as
--用来求满足条件的记录总数
declare @RecorderCount int
--总页数
declare @PageCount int
--用来执行的SQL语句
declare @pageSql nvarchar(500)
--每页的记录数与页码，参数的有效性判断
if @pageSize<1
set @pageSize=1
if @pageIndex<1
set @pageIndex=1
--排序方法的变量设置
if @sortType<>'desc' and @sortType<>'asc'
set @sortType='desc'
--输出项的有效性判断
if @outItem=''
set @outItem='*'
--查询条件的语句处理
declare @sortWh nvarchar(350)
if @sortWhere=''
set @sortWhere=' 1=1 '
--取出当前条件下的记录总数
set @pageSql='select @Record=count(*) from '+@tableName+' where '+@sortWhere
execute sp_executesql @pageSql, N'@Record int output', @Record=@RecorderCount output
--求出总的页数
if @RecorderCount%@pageSize=0
set @PageCount=@RecorderCount/@pageSize
else
set @PageCount=@RecorderCount/@pageSize+1
--输出时，加上记录总数,与总页数
set @outItem=cast(@RecorderCount as nvarchar)+' as ''RecordCount'','+
    cast(@PageCount as nvarchar)+' as ''PageCount'','+@outItem
--如果总记录数还没有单页的记录数多，则直接select
if @pageSize>=@RecorderCount or @pageIndex=1
begin
set @pageSql='select top '+cast(@pageSize as nvarchar)+' 
   '+@outItem+' from '+@tableName+' where '+@sortWhere+
   ' ORDER BY '+@sortItem+' '+@sortType
execute sp_executesql @pageSql
return
end
print(@RecorderCount)
--获取的记录数没有超出总记录范围
if @pageIndex<=@PageCount
begin
if @sortType='desc'
begin
   set @pageSql='SELECT TOP '+cast(@pageSize as nvarchar)+
    ' '+@outItem+' FROM '+@tableName+' WHERE ('+@sortItem+'<(SELECT
    Min('+ @sortItem+') FROM (SELECT TOP '+
    cast(@pageSize*(@pageIndex-1) as nvarchar)+' '+@sortItem+' FROM '+
    @tableName+' where '+@sortWhere+' ORDER BY '+@sortItem+' desc) as T)) and '+
    @sortWhere+' ORDER BY '+@sortItem+' desc'
end
else
begin
   set @pageSql='SELECT TOP '+cast(@pageSize as nvarchar)+
    ' '+@outItem+' FROM '+@tableName+' WHERE ('+@sortItem+'>(SELECT
    Max('+ @sortItem+') FROM (SELECT TOP '+
    cast(@pageSize*(@pageIndex-1) as nvarchar)+' '+@sortItem+' FROM '+
    @tableName+' where '+@sortWhere+' ORDER BY '+@sortItem+' asc) as T)) and '+
    @sortWhere+' ORDER BY '+@sortItem+' asc'
endend
else
begin
--如果设置的页码，大于总页数，则退出
return
end
print(@pageSql)
execute sp_executesql @pageSql测试了一下，执行效率还可以；测试的代码实例：go 
declare @s datetime
declare @e datetime
set @s=getdate()
--execute Pagination 3,1,'article','art_id','asc','','art_atit!='''''
execute Pagination 100,117,'article','art_id','art_id,art_atit','desc','art_atit<>'''''
set @e=getdate() 
print('所用时间：'+cast(datediff(ms,@s,@e) as nvarchar))
go输出的信息如

Answer 3

你的数据库版本是2000么??如果是2000就用这个吧  如果是2005+ 还是建议使用ROW_NUMBER()OVER

Answer 4

三种分页方法
http://topic.csdn.net/u/20100203/17/8F916471-597D-481A-B170-83BCEFE3B199.html
SQLServer 2005通用分页存储过程
通过SQLServer 2005之后新加入的ROW_NUMBER()函数进行分页：setANSI_NULLSONsetQUOTED_IDENTIFIERONgo--=============================================--Author:        <Author,,Name>--Description:    <通用分页存储过程>--=============================================CreatePROCEDURE[dbo].[ClientNewsPager]@talbeNamevarchar(255)='',--表名@getFieldsvarchar(1000)='*',--需要返回的列@orderByvarchar(255)='',--排序的字段名@PageSizeint=15,--页尺寸@PageIndexint=1,--页码,从1开始@doCountbit=0,--返回记录总数, 非 0 值则返回@whereStrvarchar(1500)=''--查询条件 (注意: 不要加 where)ASBEGINdeclare@rowResultintBeginTrandeclare@strSQLvarchar(5000)declare@startRowIndexintdeclare@maximumRowsintset@startRowIndex=(@PageIndex-1)*@PageSizeset@maximumRows=@startRowIndex+@PageSizeif(@doCount!=0)beginif@whereStr!=''set@strSQL='select id from'+@talbeName+'where'+@whereStrelseset@strSQL='select id from'+@talbeNameendelsebeginif(@whereStr!='')beginset@strSQL='SELECT'+@getFields+'FROM (SELECT ROW_NUMBER() OVER (ORDER BY'+@orderBy+') AS RowsNum,'+@getFields+'FROM'+@talbeName+'where'+@whereStr+') AS TMP WHERE (RowsNum >'+str(@startRowIndex)+'AND RowsNum <='+str(@maximumRows)+')'endelsebeginset@strSQL='SELECT'+@getFields+'FROM (SELECT ROW_NUMBER() OVER (ORDER BY'+@orderBy+') AS RowsNum,'+@getFields+'FROM'+@talbeName+') AS TMP WHERE (RowsNum >'+str(@startRowIndex)+'AND RowsNum <='+str(@maximumRows)+')'endendexec(@strSQL)Set@rowResult=@@ROWCOUNTIf@@Error<>0BeginRollBackTranReturn-1EndElseBeginCommitTranReturn@rowResultEndEND