用於分頁的預存程序

來源:互聯網
上載者:User
預存程序|分頁 /*該預存程序用於顯示註冊使用者的分頁*/
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


相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。