公用分页存储过程

忙了一上午，现在终于能停下手来。今天整理下桌面脚本，看到之前自己所编写过的公用分页存储过程，小弟在此献丑。在此之前有发布过一篇高效ms sql分页存储过程解释如何让分页脚本提高执行性能。

共有两个分页存储过程：查询字段在单表中，查询字段分布在多表；

--查询字段在单表中
--公用分页存储过程
alter procedure [dbo].[Erp_Common_PageList]
@pageIndex int,--页索引
@pageSize int,--每页显示条数
@count int,--总数
@id varchar(40),--主表主键
@tabel varchar(35),--主表名
@sortparam varchar(50),--排序
@selectParam nvarchar(2000),--查询字段字符串
@condition nvarchar(4000),--筛选条件
@relation nvarchar(4000)--关联表
as
declare @sql nvarchar(4000),@min int,@max int
set @min=(@pageIndex-1)*@pageSize+1
set @max=@pageIndex*@pageSize
set @sql='if @count=0 begin
    set @count=(select count('+@id+') from '+@tabel+' WITH (NOLOCK) '+@condition+')
end
select '+@selectParam+',@count as num from '+@tabel+@relation+' where '+@id+' in (select top '+
cast(@pageSize as varchar(5))+' '+@id+' from (select '+@id+',ROW_NUMBER() OVER (ORDER BY '+@sortparam+')
AS RowNumber from '+@tabel+' WITH (NOLOCK) '+@condition+') as lind where RowNumber between @min and @max)'
EXECUTE sp_executesql @sql,N'@count int,@min int,@max int',@count,@min,@max
GO

--查询字段分布在多表
--综合公用分页存储过程
alter procedure [dbo].[Erp_Common_PageList_TemTableByRelation]
@pageIndex int,--页索引
@pageSize int,--每页显示条数
@count int,--总数
@id varchar(40),--主表主键
@sortparam varchar(50),--排序
@selectParam nvarchar(2000),--查询字段
@condition nvarchar(2000),--查询条件
@temTable nvarchar(3000),--临时表字符串
@dropTable varchar(300),--删除临时表字符串
@relation varchar(2000),--关联表
@insrtParam varchar(100),--插入到统计总量临时表的字段
@param varchar(100)--插入到页显示的临时表字段
as
declare @sql nvarchar(4000),@min int,@max int,@date varchar(15),@tem1 varchar(20),@tem2 varchar(20),@part2 nvarchar(4000)
set @date=replace(CONVERT(varchar,getdate(),114),':','')
set @tem1='#page1'+@date
set @tem2='#page2'+@date
set @min=(@pageIndex-1)*@pageSize+1
set @max=@pageIndex*@pageSize
set @sql='
select '+@id+' as spkid'+@insrtParam+',ROW_NUMBER() OVER (ORDER BY '+@sortparam+') AS RowNumber into '+@tem1+' from '+@condition+'
select spkid,RowNumber'+@param+' into '+@tem2+' from '+@tem1+' where RowNumber between '+CAST(@min as varchar(11))+' and '+
CAST(@max as varchar(11))
if @count=0 begin
    set @part2='declare @count int
        set @count=(select max(RowNumber) from '+@tem1+')
        if @count is null set @count=0
        select  '+@selectParam+',@count as num'
end else begin
    set @part2='select  '+@selectParam+','+CAST(@count as varchar(11))+' as num'
end
set @part2=@part2+',RowNumber from '+@tem2+' as pt join '+@relation+' order by pt.RowNumber
truncate table '+@tem1+'
truncate table '+@tem2+'
drop table '+@tem1+'
drop table '+@tem2+'
'+@dropTable
--关闭影响行数
exec('set nocount on
'+@temTable+@sql+@part2+'
set nocount off')
--打开影响行数
GO

下载地址：高效公用分页存储过程  

提供源码及示例