存储过程分页

 

/*
说明：高效率存储过程分页
版本：ALL
作者：HUGO.CM（mingming0201@vip.qq.com）
时间：2010年7月8日 01:42:02

注：大家可以任意转载。如果在本人的语句上做修改的话，也请发到我邮箱一份。非常感谢 
*/
if exists (select 1 from sysobjects where id = object_id('p_paging') and xtype = 'p')
    drop proc p_paging
go

Create proc p_paging
@tableName varchar(8000),        --表名、视图名
@indexCol varchar(50),            --标识列名（如：比如主键、标识，推荐使用索引列）
@pageSize int = 10,                --页面大小
@pageIndex int = 0,                --当前页
@orderby varchar(100) = 'ASC',    --顺序|倒序 (如：ASC | DESC)
@orderCol varchar(100) = '',    --排序 (如：id)
@where varchar(1000) = '',        --条件
@columns varchar(500) = '*'        --要显示的列

as
declare @sql varchar(8000)   --拼接sql语句
declare @sql2 varchar(8000)   --拼接sql语句
declare @where2 varchar(1000)  --条件

if @where <> '' 
begin 
    select @where2 = ' and ' + @where
    select @where = ' where ' + @where
end
else
    select @where2 = ''

if @orderCol = ''
    select @orderCol = @indexCol

if @pageIndex > 0
begin
    select @sql = 'Select Top ' + Convert(varchar(50),@pageSize) + ' ' + @columns + ' FROM ' + @tableName
    select @sql = @sql + ' WHERE (' + @indexCol + ' > (SELECT MAX(' + @indexCol + ') FROM (SELECT TOP ' 
    select @sql = @sql + Convert(varchar(50),(@pageSize * @pageIndex)) + ' ' + @indexCol 
    select @sql = @sql + ' FROM ' + @tableName + @where + ' ORDER BY ' + @indexCol + ') T))' + @where2 
    select @sql2 = @sql
    select @sql = @sql + ' ORDER BY ' + @orderCol
    --根据查询出的数据段再排序
    select @sql = 'Select * from (' + @sql + ') T ORDER BY ' + @orderCol + ' ' + @orderby
    --查询数据 
    exec(@sql)
    --select @sql
    --获取总行数（有BUG存在，页面大小比能大于总数据行数，否则返回的总行数和设置的页面大小一样）
    select @sql = Replace(@sql2,'Top ' + Convert(varchar(50),@pageSize) + ' ' + @columns,'count(1) + ' + Convert(varchar(50),@pageSize * @pageIndex))
    exec(@sql)
end
else
begin
    select @sql = 'Select Top ' + Convert(varchar(50),@pageSize) + ' ' + @columns + ' FROM ' + @tableName + @where 
    select @sql2 = @sql
    select @sql =  @sql + ' ORDER BY ' + @orderCol
    select @sql
    --根据查询出的数据段再排序
    select @sql = 'Select * from (' + @sql + ') T ORDER BY ' + @orderCol + ' ' + @orderby
    --查询数据
    exec(@sql)
    --select @sql    
    select @sql2 = Replace(@sql2,'Top ' + Convert(varchar(50),@pageSize) + ' ' + @columns,'count(1)')
    --获取总行数
    exec(@sql2) 
end

 

Create proc p_Paging
@tableName varchar(8000),          --表名、视图名
@indexCol varchar(50) = 'id',      --标识列名（如：比如主键、标识，推荐使用索引列）
@pageSize int = 10,                --页面大小
@pageIndex int = 0,                --当前页
@orderCol varchar(100) = 'id desc',--排序 (如：id)
@where varchar(1000) = '',         --条件
@columns varchar(500) = '*'        --要显示的列
as
declare @sql varchar(8000)
declare @sql2 varchar(8000)
declare @where2 varchar(500)

if @where <> '' 
begin 
    select @where2 = ' And ' + @where
    select @where = ' Where ' + @where
end
else
    select @where2 = ''

select @sql = 'Select Top ' + Convert(varchar(10),@pageSize) + ' ' + @columns + ' From ' + @tableName + ' Where '
select @sql = @sql + '(' + @indexCol + ' Not In (Select Top ' + Convert(varchar(10), @pageSize * @pageIndex) + ' ' + @indexCol + ' From ' + @tableName + @where +  ' Order by '+ @orderCol +'))'
select @sql = @sql + @where2 
select @sql2 = @sql
select @sql = @sql + ' Order by ' + @orderCol
--获取数据集
exec (@sql)
select @sql2 = Replace(@sql2,'Top ' + Convert(varchar(10), @pageSize) + ' ' + @columns,'count(1)')
--获取总数据条数
exec(@sql2)