SqlServer2005中的row_number分页存储过程，支持多表联查

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



/*
支持多表查询分页存储过程(事理改进)2012.3
--多表联查1
declare @Count int
exec [proc_DataPagination] 'SL_Article a,SL_User u','u.RealName,a.*','a.UserId=u.UserId','',1,20,0,@Count output
select @Count
--多表联查2
declare @Count int
exec proc_DataPagination 'SL_LANAndWANPermissionLog l left join SL_Plate p on l.PlateId=p.PlateId left join SL_Admin a on l.AddUserId=a.UserId','l.*,p.PlateName,a.RealName as AddUserRealName','','Id',1,20,0,@Count output
select @Count
*/
/*注意:多表联查,如果两个表有相同的列名,必须指定要查询的列名,不然会报错*/
ALTER PROCEDURE [dbo].[proc_DataPagination]
(
    @Table nvarchar(1000),--表名,支持多表联查
    @Fields varchar(2000) = N'*',--字段名
    @Where nvarchar(4000) = N'',--where条件,不需要加where
    @OrderBy varchar(1000) = N'',--排序条件，不需要加order by
    @CurrentPage int = 1, --当前页,从1开始,不是0
    @PageSize int = 10,--每页显示多少条数据
    @GetCount int =0,--获取的记录总数，0则获取记录总数，不为0则不获取
    @Count int = 0 output--总数
)
AS
BEGIN
    SET NOCOUNT ON
    --没有提供排序字段,默认主键排序
    if @OrderBy is null or @OrderBy=''
    begin
        declare @tempTable varchar(200)
        set @Table=ltrim(rtrim(@Table))--去除开头和尾部空格
        --多表联查如果没有提供排序字段,自动找第一个表的主键进行排序
        if charindex(' on ',@Table)>0
            set @tempTable=substring(@Table,0,charindex(' ',@Table))
        else if charindex(',',@Table)>0
            begin
                set @tempTable=substring(@Table,0,charindex(',',@Table))
                --如果有别名如Article a,User u
                if(charindex(' ',@tempTable)>0)
                    set @tempTable=substring(@tempTable,0,charindex(' ',@tempTable))
            end
        else
            begin
                if(charindex(' ',@Table)>0)--SL_Article a防止只有一个表取别名情况
                    set @tempTable=substring(@Table,0,charindex(' ',@Table))
                else
                    set @tempTable=@Table--单表查询
            end

        --查询表是否存在
        if not exists(select * from sysobjects where [name]=@tempTable)
          begin
            raiserror('查询表%s不存在',12,12,@tempTable)
            return
          end    

        --查询排序主键
        declare @objectid int;Set @objectid=object_id(@tempTable)
        select top 1 @OrderBy=col_name(@objectid,colid) from sysobjects as o 
            Inner Join sysindexes as i On i.name=o.name 
            Inner Join sysindexkeys as k On k.indid=i.indid 
        Where o.xtype = 'PK' and parent_obj=@objectid and k.id=@objectid
        --如果没有主键,如视图
        if @OrderBy is null or @OrderBy = ''
          begin
            raiserror('%s必须在@OrderBy中提供排序字段',12,12,@tempTable)
            return
          end
    end

    --分页大小
    if @PageSize < 1
       set @PageSize=10

    --默认当前页
    if @CurrentPage < 1
        set @CurrentPage = 1

    --选取字段
    if @Fields is null or @Fields = ''
        set @Fields='*'

    --过滤条件
    if @Where is null or @Where=''
        set @Where=''
    else
        set @Where=' WHERE '+@Where

    /*设置分页参数*/
    declare @startRow varchar(50),@endRow varchar(50)
    set @startRow = cast(((@CurrentPage - 1)*@PageSize + 1) as nvarchar(50))
    set @endRow = cast(@CurrentPage*@PageSize as nvarchar(50))
    
    exec
    (
        'SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY '+@OrderBy+') AS rownumber,'+@Fields+
        ' FROM '+@Table+@Where+') AS tempdt WHERE rownumber BETWEEN '+@startRow+' AND '+@endRow
    )

    /*
    如果@GetCount=0,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,
    把总页数传回给存储过程,避免再次计算总页数,当数据量很大时,select count(*)速度也要几秒钟)
    */
    if(@GetCount=0)
      begin
        declare @sql nvarchar(max)
        set @sql='SELECT @i=COUNT(*) FROM '+@Table+@Where    
        execute sp_executesql @sql,N'@i int out',@Count OUT--返回总记录数
      end
    else
        set @Count=@GetCount
END

 

SQL Server 2005 千万条以上记录分页数据库优化经验

https://wenku.baidu.com/view/8ab21eef856a561252d36f99.html