分页存储过程 sql

说明：

分页存储过程实现大同小异，主要原理是：按照外部参数（表名，页码，页大小，返回的列 ，返回记录总数，查询条件等）获取相应数据的排序，按照条件约束获取Top N 行，再返回数据。

在这里我们可以用多种方法实现这个功能。如最简单的方法：

SELECT * FROM table WHERE 条件 ORDER BY 排序 LIMIT ((页码-1)*页大小),页大小;

但是大多数这些方法针对数据较小是使用合适，但大数据时就不见的适用。

此处我们使用一个较为均衡的方法ROW_NUMBER()方法。注：SQLServer2000没有ROW_NUMBER()方法

SELECT TOP 页大小 * FROM 
(
    SELECT TOP (页码*页大小) ROW_NUMBER() OVER (ORDER BY 排序条件) AS RowNum, * FROM table WHERE 查询条件
) AS tempTable
WHERE RowNum BETWEEN (页码-1)*页大小+1 AND 页码*页大小
ORDER BY RowNum

示例：

USE [DBName] 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create  PROCEDURE [dbo].[_Pagers]
@tableName nvarchar(255), -- 表名
@selectFields nvarchar(max) = '*', -- 需要返回的列 
@pageSize int = 10, -- 页尺寸 
@pageIndex int = 0, -- 页码 
@orderField nvarchar(max) = '', -- 排序字段,若为空,则赋值为ID
@orderType bit = 0, --排序类型(asc=0)
@condition nvarchar(max) = '' ,-- 查询条件 (注意: 不要加 where) 
@counts  int  out -- 返回记录总数, 非 0 值则返回 这里作运行后返回的记录总数分页用
AS 
set nocount on  --关闭返回计数
declare @strSQL nvarchar(max) -- 主语句 
declare @countsQL nvarchar(max)
declare @rtnCount int
declare @orderSQL nvarchar(max)
declare @conditionSQL nvarchar(max)
declare @tmpTableName nvarchar(max)
declare @tmpColumnName nvarchar(max)
set @pageIndex=@pageIndex+1
if (@orderField <> '')
begin
	if(@orderType=0)
		set @orderSQL=' order by '+@orderField+' ASC desc'
	else
		set @orderSQL=' order by '+@orderField+' DESC desc'
end
else
	if(@orderType=0)
		set @orderSQL=' order by ID ASC'
	else
		set @orderSQL=' order by ID DESC'

if @condition <> ''
	set @conditionSQL = ' where ' + @condition
else
	set @conditionSQL = ''

set @countsQL='select @counts=count(*) from  [' + @tableName + ']'+@conditionSQL--获取记录行数
exec sp_executesql @countsQL, N'@counts int out ',@counts out--执行记录行数查询

--验证分页参数
if @pageIndex<1 set @pageIndex = 1
if @pageSize<1 set @pageSize = 10
--计算要返回的纪录行数
if @counts < @pageIndex*@pageSize
	set @rtnCount = @counts-(@pageIndex-1)*@pageSize
else
	set @rtnCount = @pageSize
if @rtnCount < 0 set @rtnCount = 0
set @tmpTableName='tbl_'+cast(newid() as varchar(36))
set @tmpColumnName = 'col_'+cast(newid() as varchar(36))
set @tmpTableName=replace(@tmpTableName,'-','_')
set @tmpColumnName=replace(@tmpColumnName,'-','_')
set @strSQL = 'select * from ('
set @strSQL = @strSQL+ ' select  ROW_NUMBER() OVER('+@orderSQL+') AS rownum,'+@selectFields
+ ' from [' + @tableName +']' + @conditionSQL 
+ ') as t where  rownum between '+str((@pageIndex-1)*@pageSize+1) +' and ' +str(@pageIndex*@pageSize)
-------------------------End------------------------------------
exec (@strSQL)--执行查询
print @strSQL
set nocount off