存储过程|分页/*该存储过程用于显示注册用户的分页*/
CREATE PROCEDURE usp_PagedUserReg
     iPage      int,
     iPageSize  int
AS
Begin

--关闭自动计数器功能
SET NOCOUNT ON

--declare variables

declare  iStart       int          -- start record
declare  iEnd         int          -- end  record
declare  iPageCount   int        -- total number of pages

--  create the temporary table 建临时表
Create Table #PagedUserReg
(
   id        int    identity,
   UserID    int(4)        ,    
   Nick            char(20)    ,    
   Truename    char(10)    ,    
   email    char(100)    ,    
   department    char(50)    ,
   zhuanye    char(50)    ,
   mnianji    char(50)    ,
   sex        char(10)    ,
   birthday    datetime    ,    
   pwd        char(20)    ,
   room        char(10)    ,
   telphon    char(50)    ,
   qustion    char(100)    ,
   answer    char(50)    ,
   imagepath    char(100)    
)

-- populate the temp table 加入数据
insert into #PagedUserReg (Userid,Nick,Truename,email,department,
         zhuanye,mnianji,sex,birthday,pwd,room,telphon,qustion,answer,
         imagepath)
select  Userid,Nick,Truename,email,department,
        zhuanye,mnianji,sex,birthday,pwd,room,telphon,qustion,answer,
        imagepath
From RegUser

-- work out how many pages there are in total  计算总页数
select ipageCount=Count(*)
from RegUser

select ipageCount = Ceiling(iPageCount / iPageSize)+1

-- Check the Page number
if iPage <1
   select ipage=1

if iPage>ipageCount
   select ipage = ipageCount

-- calculate the start and end records
select iStart = (iPage-1) * iPageSize
select iEnd = istart + ipageSize + 1

-- select only those records that fall within our page
select * From #PagedUserReg
         where ID > iStart
         and   ID < iEnd

Drop Table #PagedUserReg

-- turn back on record counts
set nocount off

-- return the number of records left
Return iPageCount




end