分页存储过程
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