存储过程|分页|函数|存储过程|分页|函数
ALTER PROCEDURE Pagination
(
tblName varchar(255), -- 表名
strGetFields varchar(1000) = '*', -- 需要返回的列
fldName varchar(255)='', -- 排序的字段名
PageSize int = 10, -- 页尺寸
PageIndex int = 1, -- 页码
doCount bit = 0, -- 返回记录总数, 非 0 值则返回
OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
)
AS
/* 定义变量 */
declare strSQL varchar(5000) -- 主语句
declare strTmp varchar(110) -- 临时变量
declare strOrder varchar(400) -- 排序类型
if doCount != 0
begin
if strWhere !=''
set strSQL = 'select count(*) as Total from [' + tblName + '] where '+strWhere
else
set strSQL = 'select count(*) as Total from [' + tblName + ']'
end
--以上代码的意思是如果doCount传递过来的不是0,就执行总数统计。以下的所有代码都是doCount为0的情况
else
begin
if OrderType != 0 --如果OrderType不是0,就执行降序,这句很重要!
begin
set strTmp = '<(select min'
set strOrder = ' order by [' + fldName +'] desc'
end
else
begin
set strTmp = '>(select max'
set strOrder = ' order by [' + fldName +'] asc'
end
if PageIndex = 1
begin
if strWhere != ''
set strSQL = 'select top ' + str(PageSize) +' '+strGetFields+ ' from [' + tblName + '] where ' + strWhere + ' ' + strOrder
else
set strSQL = 'select top ' + str(PageSize) +' '+strGetFields+ ' from ['+ tblName + '] '+ strOrder
--如果是第一页就执行以上代码,这样会加快执行速度
end
else
begin
--以下代码赋予了strSQL以真正执行的SQL代码
set strSQL = 'select top ' + str(PageSize) +' '+strGetFields+ ' from ['+ tblName + '] where [' + fldName + ']' + strTmp + '(['+ fldName + ']) from (select top ' + str((PageIndex-1)*PageSize) + ' ['+ fldName + '] from [' + tblName + ']' + strOrder + ') as tblTmp)'+ strOrder
if strWhere != ''
set strSQL = 'select top ' + str(PageSize) +' '+strGetFields+ ' from [' + tblName + '] where [' + fldName + ']' + strTmp + '([' + fldName + ']) from (select top ' + str((PageIndex-1)*PageSize) + ' [' + fldName + '] from [' + tblName + '] where ' + strWhere + ' ' + strOrder + ') as tblTmp) and ' + strWhere + ' ' + strOrder
end
end
exec (strSQL)