分頁預存程序集

 
http://focus755.com/article5.asp?article_id=956

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

create  PROCEDURE sh_page
/*
***************************************************************
**  中國無憂商務網千萬數量級分頁預存程序                     **
***************************************************************
參數說明:
1.Tables             :表名稱,視圖
2.PrimaryKey         :主關鍵字
3.Sort               :排序語句,不帶Order By 比如：NewsID Desc,OrderRows Asc
4.CurrentPage        :當前頁碼
5.PageSize           :分頁尺寸
6.Filter             :過濾語句,不帶Where
7.Group             :Group語句,不帶Group By

著作權：中國無憂商務網(http://www.cn5135.com)
歡迎試用,有什麼心得請 Mail:xzhijun@GMail.com
範例程式碼：http://www.cn5135.com/Opportunities/Pagination.asp
註：轉載請保留相關著作權說明,謝謝！^_^
***************************************************************/
(
@Tables varchar(1000),
@PrimaryKey varchar(100),
@Sort varchar(200) = NULL,
@CurrentPage int = 1,
@PageSize int = 10,
@Fields varchar(1000) = ＇*＇,
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL
)
AS

/*預設排序*/
IF @Sort IS NULL OR @Sort = ＇＇
SET @Sort = @PrimaryKey

DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int

/*設定排序語句.*/
IF CHARINDEX(＇DESC＇,@Sort)＞0
BEGIN
SET @strSortColumn = REPLACE(@Sort, ＇DESC＇, ＇＇)
SET @operator = ＇＜=＇
END
ELSE
BEGIN
IF CHARINDEX(＇ASC＇, @Sort) = 0
SET @strSortColumn = REPLACE(@Sort, ＇ASC＇, ＇＇)
SET @operator = ＇＞=＇
END

IF CHARINDEX(＇.＇, @strSortColumn) ＞ 0
BEGIN
SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX(＇.＇,@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX(＇.＇,@strSortColumn) + 1, LEN(@strSortColumn))
END
ELSE
BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END

SELECT @type=t.name, @prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName

IF CHARINDEX(＇char＇, @type) ＞ 0
   SET @type = @type + ＇(＇ + CAST(@prec AS varchar) + ＇)＇

DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strSimpleFilter varchar(1000)
DECLARE @strGroup varchar(1000)

/*預設當前頁*/
IF @CurrentPage ＜ 1
SET @CurrentPage = 1

/*設定分頁參數.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))

/*篩選以及分組語句.*/
IF @Filter IS NOT NULL AND @Filter != ＇＇
BEGIN
SET @strFilter = ＇ WHERE ＇ + @Filter + ＇ ＇
SET @strSimpleFilter = ＇ AND ＇ + @Filter + ＇ ＇
END
ELSE
BEGIN
SET @strSimpleFilter = ＇＇
SET @strFilter = ＇＇
END
IF @Group IS NOT NULL AND @Group != ＇＇
SET @strGroup = ＇ GROUP BY ＇ + @Group + ＇ ＇
ELSE
SET @strGroup = ＇＇

/*執行查詢語句*/
EXEC(
＇
DECLARE @SortColumn ＇ + @type + ＇
SET ROWCOUNT ＇ + @strStartRow + ＇
SELECT @SortColumn=＇ + @strSortColumn + ＇ FROM ＇ + @Tables + @strFilter + ＇ ＇ + @strGroup + ＇ ORDER BY ＇ + @Sort + ＇
SET ROWCOUNT ＇ + @strPageSize + ＇
SELECT ＇ + @Fields + ＇ FROM ＇ + @Tables + ＇ WHERE ＇ + @strSortColumn + @operator + ＇ @SortColumn ＇ + @strSimpleFilter + ＇ ＇ + @strGroup + ＇ ORDER BY ＇ + @Sort + ＇
＇
)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

/*
  經測試,在 14483461 條記錄中查詢第 100000 頁,每頁 10 條記錄按升序和降序第一次時間均為 0.47 秒,第二次時間均為 0.43 秒,測試文法如下：
  exec GetRecordFromPage news,newsid,10,100000
  news 為 表名, newsid 為關鍵字段, 使用時請先對 newsid 建立索引。
*/

 

/*
  函數名稱: GetRecordFromPage
  函數功能: 擷取指定頁的資料
  參數說明: @tblName      包含資料的表名
           @fldName      關鍵字段名
           @PageSize     每頁記錄數
           @PageIndex    要擷取的頁碼
           @OrderType    排序類型, 0 - 升序, 1 - 降序
           @strWhere     查詢條件 (注意: 不要加 where)
  作　　者: 鐵拳
  郵　　箱: sunjianhua_kki@sina.com
  建立時間: 2004-07-04
  修改時間: 2004-07-04
*/
CREATE PROCEDURE GetRecordFromPage
    @tblName      varchar(255),       -- 表名
    @fldName      varchar(255),       -- 欄位名
    @PageSize     int = 10,           -- 頁尺寸
    @PageIndex    int = 1,            -- 頁碼
    @OrderType    bit = 0,            -- 設定排序類型, 非 0 值則降序
    @strWhere     varchar(2000) = ＇＇  -- 查詢條件 (注意: 不要加 where)
AS

declare @strSQL   varchar(6000)       -- 主語句
declare @strTmp   varchar(1000)       -- 臨時變數
declare @strOrder varchar(500)        -- 排序類型

if @OrderType != 0
begin
    set @strTmp = ＇＜(select min＇
    set @strOrder = ＇ order by [＇ + @fldName + ＇] desc＇
end
else
begin
    set @strTmp = ＇＞(select max＇
    set @strOrder = ＇ order by [＇ + @fldName +＇] asc＇
end

set @strSQL = ＇select top ＇ + str(@PageSize) + ＇ * from [＇
    + @tblName + ＇] where [＇ + @fldName + ＇]＇ + @strTmp + ＇([＇
    + @fldName + ＇]) from (select top ＇ + str((@PageIndex-1)*@PageSize) + ＇ [＇
    + @fldName + ＇] from [＇ + @tblName + ＇]＇ + @strOrder + ＇) as tblTmp)＇
    + @strOrder

if @strWhere != ＇＇
    set @strSQL = ＇select top ＇ + str(@PageSize) + ＇ * from [＇
        + @tblName + ＇] where [＇ + @fldName + ＇]＇ + @strTmp + ＇([＇
        + @fldName + ＇]) from (select top ＇ + str((@PageIndex-1)*@PageSize) + ＇ [＇
        + @fldName + ＇] from [＇ + @tblName + ＇] where ＇ + @strWhere + ＇ ＇
        + @strOrder + ＇) as tblTmp) and ＇ + @strWhere + ＇ ＇ + @strOrder

if @PageIndex = 1
begin
    set @strTmp = ＇＇
    if @strWhere != ＇＇
        set @strTmp = ＇ where (＇ + @strWhere + ＇)＇

    set @strSQL = ＇select top ＇ + str(@PageSize) + ＇ * from [＇
        + @tblName + ＇]＇ + @strTmp + ＇ ＇ + @strOrder
end

exec (@strSQL)

GO

 

 

實現資料分頁(1)
/*
作者:郭新行
功能:實現資料分頁
日期:2004-11-11
描述:此過程功能實現對資料的分頁,輸入的參數中 @SelectFieldName,@tbName,@strWhere,
     @OrderFieldName 合起來為一個完整的Select查詢語句,此語句中不能包含中間表
     @OrderFieldName 必須是可以作為主鍵的欄位
*/
ALTER       PROCEDURE usp_GetRecordFromPage
    @tblName       varchar(1000),        -- 表名
    @SelectFieldName    varchar(4000),              -- 要顯示的欄位名(不要加select)
    @strWhere       varchar(4000),              -- 查詢條件(注意: 不要加 where)
    @OrderFieldName      varchar(255),               -- 排序索引欄位名
    @PageSize       int ,                 -- 頁大小
    @PageIndex      int = 1,                  -- 頁碼
    @iRowCount      int output,                 -- 返回記錄總數
    @OrderType      bit = 0                  -- 設定排序類型, 非 0 值則降序
          
AS

DECLARE @strSQL    VARCHAR(4000)       -- 主語句
DECLARE @strTmp    VARCHAR(4000)       -- 臨時變數
DECLARE @strOrder  VARCHAR(1000)       -- 排序類型
DECLARE @strRowCount    NVARCHAR(4000)      -- 用於查詢記錄總數的語句

SET @OrderFieldName=ltrim(rtrim(@OrderFieldName))
SET @SelectFieldName=ltrim(rtrim(@SelectFieldName))
SET @strWhere =ltrim(rtrim(@strWhere))

IF @SelectFieldName=＇＇
BEGIN
    SET @SelectFieldName=＇ * ＇
END

IF @OrderType != 0
BEGIN
    SET @strTmp = ＇＜(select min＇
    SET @strOrder = ＇ order by ＇ + @OrderFieldName +＇ desc＇
END
ELSE
BEGIN
    SET @strTmp = ＇＞(select max＇
    SET @strOrder = ＇ order by ＇ + @OrderFieldName +＇ asc＇
END

IF @strWhere != ＇＇
BEGIN
    SET @strSQL = ＇select top ＇ + ltrim(rtrim(str(@PageSize)))+＇ ＇ + @SelectFieldName+＇ from ＇
        + @tblName + ＇ where ＇ + @OrderFieldName + @strTmp + ＇(＇
        + right(@OrderFieldName,len(@OrderFieldName)-charindex(＇.＇,@OrderFieldName)) + ＇) from (select top ＇ + ltrim(rtrim(str((@PageIndex-1)*@PageSize)))+＇ ＇
        + @OrderFieldName + ＇ from ＇ + @tblName + ＇ where ＇ + @strWhere + ＇ ＇
        + @strOrder + ＇) as tblTmp) and ＇ + @strWhere + ＇ ＇ + @strOrder
END 
ELSE 
BEGIN
    SET @strSQL = ＇select top ＇ + ltrim(rtrim(str(@PageSize)))+＇ ＇ + @SelectFieldName+＇ from ＇
        + @tblName + ＇ where ＇ + @OrderFieldName + @strTmp + ＇(＇
        + right(@OrderFieldName,len(@OrderFieldName)-charindex(＇.＇,@OrderFieldName)) + ＇) from (select top ＇ + ltrim(rtrim(str((@PageIndex-1)*@PageSize)))+＇ ＇
        + @OrderFieldName + ＇ from ＇ + @tblName  + @strOrder + ＇) as tblTmp)＇
        + @strOrder
END

IF @PageIndex = 1
BEGIN
    SET @strTmp = ＇＇
    IF @strWhere != ＇＇
    BEGIN
        SET @strTmp = ＇ where ＇ + @strWhere
    END
    SET @strSQL = ＇select top ＇ + ltrim(rtrim(str(@PageSize)))+ ＇ ＇ + @SelectFieldName+＇ from ＇
                 + @tblName + @strTmp + ＇ ＇ + @strOrder
END

PRINT(@strSQL)
EXEC(@strSQL)
 
IF @strWhere!=＇＇
BEGIN
  SET @strRowCount = ＇select @iRowCount=count(*) from ＇ + @tblName+＇ where ＇+@strWhere
END
ELSE
BEGIN
  SET @strRowCount = ＇select @iRowCount=count(*) from ＇ + @tblName
END

EXEC sp_executesql @strRowCount,N＇@iRowCount int out＇,@iRowCount out