SQL存储过程分页
一,常用的存储过程
CREATE PROCEDURE sp_GetRecordByPageOrder
@tblName varchar(255)='wdf1', -- 表名
@fldName varchar(1000) = '*', -- 需要返回的列
@OrderfldName varchar(255)='userid', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount 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 @IsReCount != 0
begin
if @strWhere !=''
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where '+@strWhere
else
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end
--以上代码的意思是如果@IsReCount传递过来的不是0，就执行总数统计。以下的所有代码都是@IsReCount为0的情况
else
begin
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @OrderfldName +'] desc'
--如果@OrderType不是0，就执行降序，这句很重要！
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @OrderfldName +'] asc'
end
if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@fldName+ ' from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) +' '+@fldName+ ' from ['+ @tblName + '] '+ @strOrder
--如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = 'select top ' + str(@PageSize) +' '+@fldName+ ' from ['
+ @tblName + '] where [' + @OrderfldName + ']' + @strTmp + '(['+ @OrderfldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @OrderfldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@fldName+ ' from ['
+ @tblName + '] where [' + @OrderfldName + ']' + @strTmp + '(['
+ @OrderfldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @OrderfldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end
end
exec (@strSQL)
GO
二、在SQL server 2005 中 多了个Row_Number() over (order by ID DESC)语句，使分页更加简单了，
意思是生成一个顺序的行号，而他生成顺序的标准，就是后面紧跟的over(order by ID Desc)
DECLARE @rows_per_page AS INTEGER
DECLARE @current_page AS INTEGER
DECLARE @total_pages AS INTEGER
DECLARE @start_item AS INTEGER
DECLARE @items_count AS INTEGER
-- 设置每页的行数
SET @rows_per_page = 10
-- 设置要显示的页号(从1开始)
SET @current_page = 3
SELECT @total_pages = COUNT(*) / @rows_per_page + 1,
@items_count= COUNT(*)
FROM BarefootIndex;--表名（这是修改的地方）
--计算此页中从第几个开始显示
SET @start_item = @rows_per_page * (@current_page - 1)
select * from
(
select ROW_NUMBER() OVER (order by ID) as item--用什么排序(返回正在显示第几条)
,@items_count AS items_count --一共有多少条
,@current_page AS current_page --当前页
,@total_pages AS total_pages --一共多少页
,* from BarefootIndex--表名（这是修改的地方）
) as T
where T.item >= @start_item + 1
AND T.item <= @start_item + @rows_per_page
