|
/*該存儲過程用于顯示注冊用戶的分頁*/
CREATE PROCEDURE usp_PagedUserReg
@iPageint,
@iPageSizeint
AS
Begin
--關閉自動計數器功能
SET NOCOUNT ON
--declare variables
declare@iStart int-- start record
declare@iEnd int-- endrecord
declare@iPageCount int-- total number of pages
--create the temporary table 建臨時表
Create Table #PagedUserReg
(
idintidentity,
UserIDint(4),
Nickchar(20),
Truenamechar(10),
emailchar(100),
departmentchar(50),
zhuanyechar(50),
mnianjichar(50),
sexchar(10),
birthdaydatetime,
pwdchar(20),
roomchar(10),
telphonchar(50),
qustionchar(100),
answerchar(50),
imagepathchar(100)
)
-- populate the temp table 加入數據
insert into #PagedUserReg (Userid,Nick,Truename,email,department,
zhuanye,mnianji,sex,birthday,pwd,room,telphon,qustion,answer,
imagepath)
selectUserid,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
|
