调用表、视图以及储存过程的分页储存过程

刚加入一个项目不久，原来用的分页是假分页。现在要改为真分页，在网上搜索了相关的分页储存过程，由于项目之前的数据获取部分比较杂乱，甚至有直接调用储存过程来获取数据。只好将搜集到的分页储存过程给修改修改，在网上查了相关资料最后分页储存过程sql代码如下(比较麻烦的是调用存储过程的时候需要动态拼接sql语句。注意修改连接字符串，可以看需求而修改相应部分总的来说比较全面了)：

------------------------------------
--用途：分页存储过程(可调用表、视图及存储过程获取数据)  
--说明：
------------------------------------
ALTER PROCEDURE [dbo].[UP_GetRecordByPage4]
    @tblName      varchar(128),       -- 表名
    @fldName      varchar(128),       -- 字段名(即按该字段排序获取数据)
    @PageSize     int = 10,           -- 页尺寸
    @PageIndex    int = 1,            -- 页码
    @OrderType    bit = 0,            -- 设置排序类型, 非 0 值则降序
    @strWhere     varchar(3000) = '', -- 查询条件 (注意: 要加 where)
    @ProcName     varchar(100)=''     --存储过程名字

AS

declare @strSQL   varchar(8000)       -- 主语句
declare @strOrder varchar(400)        -- 排序类型
declare @strConn  varchar(100)        --连接字符串



if @ProcName !=''
begin
    set @strConn='server=localhost;uid=sa;pwd=sa;database=XXXX' ---连接字符串(需修改)
    
    set @strSQL='select * into  #temp from  OpenRowSet(''SQLOLEDB'','''+
                @strConn+''',''exec '+@ProcName+''') ' + @strWhere+';'
    
    ------如果字段为text、image、ntext类型order by无法排序需要转换为varchar
    set @strSQL=@strSQL+'declare @Orderby varchar (100) if exists(select a.* from tempdb.sys.columns
                a inner join tempdb.sys.objects b on a.[object_id]=object_id(N''tempdb..#temp'')
                and a.name='''+@fldName+''' and a.system_type_id in(34,35,99))    
    begin
    if '+convert(varchar,@OrderType)+' != 0
        set @Orderby = ''order by convert(varchar,' + @fldName +') desc''
    else
        set @Orderby = ''order by convert(varchar,' + @fldName +') asc''
    end
    else
    begin
        if '+convert(varchar,@OrderType)+' != 0
            set @Orderby = ''order by ' + @fldName +' desc''
        else
            set @Orderby = ''order by ' + @fldName +' asc''    
    end;'

    set @strSQL =@strSQL+'exec(''select *,(select count(1) from #temp) as recordCount from 
                (select *,row_number() over (''+@Orderby+'') rn from #temp) a where a.rn between '+
                CAST((@PageIndex-1)*@PageSize+1 as varchar)+' and '+
                CAST(@PageIndex*@PageSize as varchar)+''')'
    exec (@strSQL)
end
else
begin
    ------如果字段为text、image、ntext类型order by无法排序需要转换为varchar
    if exists(select * from syscolumns a where a.id = object_id(@tblName)
              and a.name=@fldName and xtype in(34,35,99))
    begin
        if @OrderType != 0
            set @strOrder = 'order by convert(varchar,' + @fldName +') desc'
        else
            set @strOrder = 'order by convert(varchar,' + @fldName +') asc'
    end
    else
    begin
        if @OrderType != 0
            set @strOrder = 'order by ' + @fldName +' desc'
        else
            set @strOrder = 'order by ' + @fldName +' asc'
    end

    set @strSQL='select * into  #temp from '+@tblName +' '+ @strWhere+';'

    set @strSQL =@strSQL+'select *,(select count(1) from #temp) as recordCount'+
                ' from (select *,row_number() over ('+@strOrder+') rn from #temp)'+
                ' a where a.rn between '+CAST((@PageIndex-1)*@PageSize+1 as varchar)+
                ' and '+CAST(@PageIndex*@PageSize as varchar)
    exec (@strSQL)
end

 

需要注意的是要使用OpenRowSet函数 必须要配置SQL Server实例支持ad hoc查询，需要使用系统存储过程sp_configure先设置advanced options，再启用Ad Hoc Distributed Queries（ad hoc分布式查询）：sql脚本如下：

EXEC sp_configure 'show advanced options', 1;  
GO
RECONFIGURE; 
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE; 
GO