SQL Server 存储过程收集

在学习sql存储过程，收集了一些sql分页存储过程放在自己的Blog里，以方便以后查询。 

分页方案一：(利用Not In和SELECT TOP分页)
语句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
          (SELECT TOP 20 id
         FROM TestTable
         ORDER BY id))
ORDER BY ID

SELECT TOP 页大小 *
FROM TestTable
WHERE (ID NOT IN
          (SELECT TOP 页大小*页数 id
         FROM 表
         ORDER BY id))
ORDER BY ID

-------------------------------------

分页方案二：(利用ID大于多少和SELECT TOP分页）
语句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID >
          (SELECT MAX(id)
         FROM (SELECT TOP 20 id
                 FROM TestTable
                 ORDER BY id) AS T))
ORDER BY ID

SELECT TOP 页大小 *
FROM TestTable
WHERE (ID >
          (SELECT MAX(id)
         FROM (SELECT TOP 页大小*页数 id
                 FROM 表
                 ORDER BY id) AS T))
ORDER BY ID

-------------------------------------

分页方案三：(利用SQL的游标存储过程分页)
create  procedure XiaoZhengGe
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
 @rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页 
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off

-------------------------------------

这里抄了一个例子，你可以修改一下，添加更多的查询条件

使用时，首次调用，将@docount设为1，获得满足条件的记录行数
以后调用时，将@docount设为0，获得指定页的记录

祝你好运！

CREATE procedure pageer_rnewlist
(@pagesize int,
@pageindex int,
@docount bit,
@education varchar(50)
)
as

set nocount on
if(@docount=1)

select count(*) from resume where education=@education

else
begin

declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound

insert into @indextable(nid)
select id from resume where education=@education order by id desc

select O.* from resume O,@indextable t where O.id=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id

end
set nocount off