procedure 分页

/*1：取第一页；2：通用法分页；3：对数值类型使用的分页方法*/
GO
create PROC [dbo].[Sp_Sys_PageList]

@TblName varchar(2000), -- 表名
@Fields varchar(1000) = '*', -- 需要返回的列
@Condition varchar(1500) = '1=1', -- 查询条件
@PageSize int, -- 页尺寸
@PageIndex int = 1,
@Order varchar(255), -- 排序 asc desc
@OrderType int = 0, -- 0默认为正序 1为倒序 ; 注 与order Optype不为 3时，可不添,
@KeyId varchar(255),-- Optype不为 3时，可不添
@OpCount int = 0, --0:不取总数1:取总数
@Optype int --1:取第一页 2:两页以上

AS
if(@Optype = 1 )
begin
declare @strSQL varchar(5000) -- 主语句
set @strSQL = 'select top ' + str(@PageSize) +' '+@Fields+ ' from ' + @TblName + ' where ' + @Condition + ' order by ' + @Order

exec (@strSQL)
end

else if(@Optype = 2 )
begin
declare @minnum int,@maxnum int

/*if @PageIndex > 100 set @PageIndex = 100*/
if @pageIndex < 1 set @pageIndex=1
SET @minnum = ((@pageIndex - 1) * @PageSize)+1
SET @maxnum = @pageIndex * @PageSize

declare @strSQLRow varchar(5000) -- 主语句

set @strSQLRow = 'select * from ('

set @strSQLRow = @strSQLRow + 'select '+@Fields+ ',Row_Number() over (order by '+@Order+') as RowNum from ' + @TblName + ' where ' + @Condition

set @strSQLRow = @strSQLRow +') as tabtemp WHERE RowNum between '+str(@minnum)+' and '+str(@maxnum)
print(@strSQLRow)
exec (@strSQLRow)

end
else if(@Optype = 3) /*真对数值列 and 单字段排序 建议使用些方法*/
begin
declare @Esql varchar(5000)
declare @OrderT varchar(500)
declare @MaxMinT varchar(500)

if (@OrderType = 0)
begin
set @OrderT=@KeyId + ' > '
set @MaxMinT='max('+@KeyId+')'
end
else
begin
set @OrderT=@KeyId + ' < '
set @MaxMinT='min('+@KeyId+')'
end

set @Esql = 'select top '+Cast(@PageSize as char)+' ' +@Fields+' from '+@TblName+' where '+@OrderT+' (select '+@MaxMinT+' from (select top '+Cast((@pageIndex - 1) * @PageSize as char)+@KeyId+' from '+@TblName+' where '+@Condition+' order by '+@Order+' ) as temptab) and '+@Condition+' order by '+ @Order
exec(@Esql)
print(@Esql)
end
--取记录数
if(@OpCount=1)
begin
declare @strCountSQL varchar(3000)
set @strCountSQL = 'select count(0) as Total from ' + @TblName + ' where '+@Condition
exec (@strCountSQL)
end