分页存储过程

1.使用存储过程分页

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[premium_GetPagedReCord] 
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列 
@fldName varchar(255)='', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere nvarchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS

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

if @doCount != 0 --如果@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 

else

begin

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 @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)

 

执行存储过程：

exec premium_GetPagedReCord @tblName='INS_COMPANY_ANNUITY_TAB',
@fldName='fld_updatedate',@PageSize = 50,@PageIndex  = 1, @doCount = 0, @OrderType = 0,
@strWhere = ''

*在此提一下此存储过程有个缺点，就是当你@fldName（排序的字段名）参数为时间，但是时间每条记录的时间又完全相同时候，就只能查出第一页的数据了，因为建议你再入库程序时候如果批量插入数据可以再默认时间上加1秒就可以保证时间字段不相同了。

 

2.使用ROW_NUMBER() 分页旧版的数据库都不支持此函数

SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY @fld_name DESC) AS rownum,*
      FROM @tb_name) AS D
WHERE rownum BETWEEN (@pageIndex-1)*@pageSize+1 AND @pageIndex*@pageSize ORDER BY @fld_name DESC