一個將資料分頁的預存程序

來源:互聯網
上載者:User
預存程序|分頁|資料
CREATE PROCEDURE sp_page
  @tb         varchar(50), --表名
  @col        varchar(50), --按該列來進行分頁
  @coltype    int,         --@col列的類型,0-數字類型,1-字元類型,2-日期時間類型
  @orderby    bit,         --排序,0-順序,1-倒序
  @collist    varchar(800),--要查詢出的欄位列表,*表示全部欄位
  @pagesize   int,         --每頁記錄數
  @page       int,         --指定頁
  @condition  varchar(800),--查詢條件
  @pages      int OUTPUT   --總頁數
AS
/*
功能描述:對指定表中滿足條件的記錄按指定列進行分頁查詢,分頁可以順序、倒序
         查詢可以指定頁大小、指定查詢任意頁、指定輸出欄位列表,返回總頁數
作    者:pbsql
版    本:1.10
最後修改:2004-11-29
*/
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
IF @condition is null or rtrim(@condition)=''
BEGIN--沒有查詢條件
  SET @where1=' WHERE '
  SET @where2='  '
END
ELSE
BEGIN--有查詢條件
  SET @where1=' WHERE ('+@condition+') AND '--本來有條件再加上此條件
  SET @where2=' WHERE ('+@condition+') '--原本沒有條件而加上此條件
END
SET @sql='SELECT @pages=CEILING((COUNT(*)+0.0)/'+CAST(@pagesize AS varchar)+
         ') FROM '+@tb+@where2
EXEC sp_executesql @sql,N'@pages int OUTPUT',@pages OUTPUT--計算總頁數
IF @orderby=0
  SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
           ' FROM (SELECT">'+@tb+@where1+@col+'>(SELECT MAX('+@col+') '+
           ' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
           @col+' FROM '+@tb+@where2+'ORDER BY '+@col+') t) ORDER BY '+@col
ELSE
  SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
           ' FROM '+@tb+@where1+@col+'<(SELECT MIN('+@col+') '+
           ' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
           @col+' FROM '+@tb+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+
           @col+' DESC'
IF @page=1--第一頁
  SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+
    @where2+'ORDER BY '+@col+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
EXEC(@sql)
GO

本預存程序高效,曾用500萬條資料測試(已建索引),只返回分頁只需3秒,影響效率的地方是計算總頁數,若不需要可以注釋掉

--測試樣本
declare @pages int
select identity(int,1,1) id,getdate() dt,xx=cast('' as varchar(10)) into #t
 from sysobjects
update #t set dt=dateadd(day,id-200,dt),
              xx='xxxx'+right('000000'+cast(id as varchar(10)),6)

exec sp_page '#t','id',0,0,'*',10,2,'',@pages output--按id順序取第二頁
exec sp_page '#t','id',0,1,'*',10,2,'',@pages output--按id倒序取第二頁
exec sp_page '#t','xx',1,0,'*',10,3,'',@pages output--按xx順序取第三頁
exec sp_page '#t','xx',1,1,'*',10,3,'',@pages output--按xx倒序取第三頁
exec sp_page '#t','dt',2,0,'*',10,2,'',@pages output--按dt順序取第二頁
exec sp_page '#t','dt',2,1,'*',10,2,'',@pages output--按dt倒序取第二頁

select 總頁數=@pages

drop table #t



相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

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 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。