Stored Procedures for paging

Stored Procedure | paging/* This stored procedure is used to display the paging of the registered user * *
CREATE PROCEDURE Usp_pageduserreg
@iPage int,
@iPageSize int
As
Begin

--Turn off automatic counter function
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 to build temporary tables
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 to add data
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 the total page count
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 so fall within our page
SELECT * FROM #PagedUserReg
where ID > @iStart
and ID < @iEnd

Drop Table #PagedUserReg

--Turn back in record counts
SET NOCOUNT OFF

--Return the number of records left
Return @iPageCount




End