SQL SERVER 高效預存程序分頁(Max/Min方法)

drop procedure propageset 
go

Create Procedure ProPageSet
@tb varchar(50), --表名 
@col varchar(50), --按該列來進行分頁(必須是唯一性的字元，比如識別值種子) 
@colorder varchar(50), --需要排序的欄位(為空白，則預設為col) 
@orderby bit, --排序,0-順序,1-倒序 
@collist varchar(800),--要查詢出的欄位列表,*表示全部欄位 
@pagesize int, --每頁記錄數 
@page int, --指定頁 
@condition varchar(800),--查詢條件 
@pages int OUTPUT --總頁數
--@sqlout nvarchar(4000),---返回sql語句
 
AS
SET NOCOUNT ON
Declare @intResult Int
Begin Tran
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
IF @colorder is null or rtrim(@colorder)='' 
 set @colorder = @col
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 @intResult=COUNT(*) FROM '+@tb+@where2
EXEC sp_executesql @sql,N'@intResult int OUTPUT',@intResult OUTPUT--計算總記錄數
Select @pages=CEILING((@intResult+0.0)/@pagesize)--計算總頁數
IF @orderby=0 
SET @sql='Select TOP '+CAST(@pagesize AS varchar)+' '+@collist+ ' FROM '+@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 '+@colorder
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 '+@colorder+' DESC'
IF @page=1--第一頁 
SET @sql='Select TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+ 
@where2+'ORDER BY '+@colorder+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
--set @sqlout = @sql
EXEC(@sql)
--print 'Sql語句輸出為: ' + @sqlout
-------------------------------------------------------------------------------------------------------------------------------------------
If @@Error <> 0
  Begin
  RollBack Tran
  Return -1
  End
Else
  Begin
  Commit Tran
  Return @intResult
  End
GO

調用:
樣本:先將該預存程序在pubs中建立。

asp調用樣本:對employee表進行分頁

表結構
Create TABLE [dbo].[employee] (
 [emp_id] [empid] NOT NULL ,
 [fname] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [minit] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
 [lname] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [job_id] [smallint] NOT NULL ,
 [job_lvl] [tinyint] NULL ,
 [pub_id] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
 [hire_date] [datetime] NOT NULL 
) ON [PRIMARY]
GO

代碼檔案:

Set cmd = Server.CreateObject("ADODB.Command")
with cmd
 .ActiveConnection = conn       '資料庫連接字串
 .CommandText = "ProPageSet"       '指定預存程序名
 .CommandType = 4        '表明這是一個預存程序
 .Prepared = true        '要求將SQL命令先行編譯
 .Parameters.Append .CreateParameter("RETURN",3,4,4)   '傳回值
 .Parameters.append .CreateParameter("@tb",200,1,50,"t_admin")  '分頁時要查詢的表名
 .Parameters.append .CreateParameter("@col",200,1,50,"f_id")  '按該列來進行分頁
 .Parameters.append .CreateParameter("@colorder",200,1,50,orderfield) '排序欄位
 .Parameters.append .CreateParameter("@orderby",11,1,1,orderway)  '排序方式，0為順序，1為倒序
 .Parameters.append .CreateParameter("@collist",200,1,800,"*")  '每頁中要顯示的欄位，以逗號隔開
 .Parameters.append .CreateParameter("@pagesize",3,1,4,mypagesize) '每頁記錄數
 .Parameters.append .CreateParameter("@page",3,1,4,page)   '指定頁數
 .Parameters.append .CreateParameter("@condition",200,1,800,sqlwhere) '查詢條件where 中的條件陳述式
 .Parameters.Append .CreateParameter("@pages",3,2,4)   '總頁數output
 Set rs = .Execute
end with

while not rs.eof 
 '顯示輸出分頁的資料庫內容
 rs.movenext
wend

rs.close '取傳回值前一定要關閉，否則取不到
totalrecord = cmd(0) '總記錄數　
totalpage = cmd(9) '總頁數

set rs = nothing
set cmd = nothing

產生的Sql語句其實就是這樣的:(即每頁5條記錄，第三頁)

Select TOP 5 * FROM t_admin Where f_id>
 (Select max(f_id) FROM 
  (Select TOP 10 f_id FROM t_admin orDER BY f_id) t) 
orDER BY f_username DESC