分页的存储过程

 CREATE procedure dbo.tt_PageOut2

@dbOwner varchar(50)='dbo',
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
--@doCount bit = 0, -- 无效参数
@OrderType bit = 0, -- 设置排序类型, 1降序 0升序
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)

,@idName varchar(100)='' --标示列名称
,@idOrder bit=0 --标示列排序 1降序 0升序

AS

set nocount on

if @strWhere='' or @strWhere is null set @strWhere=' 1=1 '
if @idName=@fldName set @idName=''

declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型


--以下执行总数统计,计算总数，页数

set @strSQL = 'select count(*) as Total,ceiling(convert(decimal,count(*))/'+convert(varchar(10),@PageSize)+') as PageCount from '+@dbOwner+'.' + @tblName + ' where '+@strWhere
exec (@strSql)


--以下计算返回结果集

set @strSql=''

if @OrderType != 0 begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName +'] desc'
--如果@OrderType不是0，就执行降序，这句很重要！
end else begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end

if @idName<>'' begin
if @idOrder!=0
set @strOrder=@strOrder+', ['+@idName+'] desc'
else
set @strOrder=@strOrder+', ['+@idName+'] asc'
end

if @PageIndex = 1 begin
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from '
+@dbOwner+'.' + @tblName + ' where ' + @strWhere + ' ' + @strOrder


--如果是第一页就执行以上代码，这样会加快执行速度

end else begin

--以下代码赋予了@strSQL以真正执行的SQL代码

if @idName='' begin
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from '+@dbOwner+'.' + @tblName + ' where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder

end else begin
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['
+ @tblName + '] where [' + @idName + '] not in '
+ '(select ['+ @idName + '] from (select top '
+ str((@PageIndex-1)*@PageSize) + ' ['+ @idName + '] from ['+@dbOwner+'].['
+ @tblName + '] where '+@strWhere+' '+ @strOrder + ') as tblTmp) and '+@strWhere+' '+ @strOrder

end

end


--print @strSQL
exec (@strSQL)


set nocount off