您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

通用分頁預存程序（SQL 2000+）：TOP N 法與字串快取法完整代碼

編輯：關於SqlServer

版本一：TOP N 法（支援多欄位主鍵）

CREATE PROC sp_PageView
    @tbname       sysname,            -- 表/視圖/表值函數名稱
    @FieldKey     NVARCHAR(1000),     -- 定位用主鍵/唯一鍵，可多欄逗號分隔
    @PageCurrent  INT = 1,            -- 當前頁碼
    @PageSize     INT = 10,           -- 頁大小
    @FieldShow    NVARCHAR(1000) = '',-- 顯示欄位，逗號分隔，空白則 *
    @FieldOrder   NVARCHAR(1000) = '',-- 排序欄位，可含 DESC/ASC
    @Where        NVARCHAR(1000) = '',-- WHERE 條件
    @PageCount    INT OUTPUT          -- 總頁數
AS
SET NOCOUNT ON;
-- 物件有效性檢查
IF OBJECT_ID(@tbname) IS NULL
BEGIN RAISERROR(N'物件 "%s" 不存在',1,16,@tbname); RETURN; END
IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
   AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
   AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
BEGIN RAISERROR(N'"%s" 非表/視圖/表值函數',1,16,@tbname); RETURN; END

-- 參數規範化
IF ISNULL(@FieldKey,N'')='' BEGIN RAISERROR(N'分頁需主鍵/唯一鍵',1,16); RETURN; END
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1;
IF ISNULL(@PageSize,0)<1 SET @PageSize=10;
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*';
IF ISNULL(@FieldOrder,N'')=N'' SET @FieldOrder=N'' ELSE SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder);
IF ISNULL(@Where,N'')=N'' SET @Where=N'' ELSE SET @Where=N'WHERE ('+@Where+N')';

-- 計算總頁數（若 @PageCount 為 NULL）
IF @PageCount IS NULL
BEGIN
    DECLARE @sql NVARCHAR(4000);
    SET @sql=N'SELECT @PageCount=COUNT(*) FROM '+@tbname+N' '+@Where;
    EXEC sp_executesql @sql, N'@PageCount INT OUTPUT', @PageCount OUTPUT;
    SET @PageCount=(@PageCount+@PageSize-1)/@PageSize;
END

DECLARE @TopN VARCHAR(20), @TopN1 VARCHAR(20);
SELECT @TopN=@PageSize, @TopN1=(@PageCurrent-1)*@PageSize;

-- 第一頁直接 TOP
IF @PageCurrent=1
    EXEC(N'SELECT TOP '+@TopN+N' '+@FieldShow+N' FROM '+@tbname+N' '+@Where+N' '+@FieldOrder);
ELSE
BEGIN
    -- 別名處理
    IF @FieldShow=N'*' SET @FieldShow=N'a.*';

    -- 產生主鍵 JOIN 條件
    DECLARE @Where1 NVARCHAR(4000), @Where2 NVARCHAR(4000), @s NVARCHAR(1000), @Field SYSNAME;
    SELECT @Where1=N'', @Where2=N'', @s=@FieldKey;
    WHILE CHARINDEX(N',',@s)>0
        SELECT @Field=LEFT(@s,CHARINDEX(N',',@s)-1),
               @s=STUFF(@s,1,CHARINDEX(N',',@s),N''),
               @Where1=@Where1+N' AND a.'+@Field+N'=b.'+@Field,
               @Where2=@Where2+N' AND b.'+@Field+N' IS NULL',
               @Where=REPLACE(@Where,@Field,N'a.'+@Field),
               @FieldOrder=REPLACE(@FieldOrder,@Field,N'a.'+@Field),
               @FieldShow=REPLACE(@FieldShow,@Field,N'a.'+@Field);
    SELECT @Where=REPLACE(@Where,@s,N'a.'+@s),
           @FieldOrder=REPLACE(@FieldOrder,@s,N'a.'+@s),
           @FieldShow=REPLACE(@FieldShow,@s,N'a.'+@s),
           @Where1=STUFF(@Where1+N' AND a.'+@s+N'=b.'+@s,1,5,N''),
           @Where2=CASE WHEN @Where='' THEN N'WHERE (' ELSE @Where+N' AND (' END+N'b.'+@s+N' IS NULL'+@Where2+N')';

    -- 執行分頁查詢
    EXEC(N'SELECT TOP '+@TopN+N' '+@FieldShow+N' FROM '+@tbname+N' a LEFT JOIN (SELECT TOP '+@TopN1+N' '+@FieldKey+N' FROM '+@tbname+N' a '+@Where+N' '+@FieldOrder+N') b ON '+@Where1+N' '+@Where2+N' '+@FieldOrder);
END

版本二：字串快取法（單一主鍵欄位，效能較佳）

CREATE PROC sp_PageView
    @tbname       SYSNAME,
    @FieldKey     SYSNAME,            -- 單一主鍵欄位
    @PageCurrent  INT = 1,
    @PageSize     INT = 10,
    @FieldShow    NVARCHAR(1000) = '',
    @FieldOrder   NVARCHAR(1000) = '',
    @Where        NVARCHAR(1000) = '',
    @PageCount    INT OUTPUT
AS
DECLARE @sql NVARCHAR(4000);
SET NOCOUNT ON;
-- 物件與參數檢查（同版本一，略）

IF @PageCount IS NULL
BEGIN
    SET @sql=N'SELECT @PageCount=COUNT(*) FROM '+@tbname+N' '+@Where;
    EXEC sp_executesql @sql, N'@PageCount INT OUTPUT', @PageCount OUTPUT;
    SET @PageCount=(@PageCount+@PageSize-1)/@PageSize;
END

DECLARE @TopN VARCHAR(20), @TopN1 VARCHAR(20);
SELECT @TopN=@PageSize, @TopN1=@PageCurrent*@PageSize;

IF @PageCurrent=1
    EXEC(N'SELECT TOP '+@TopN+N' '+@FieldShow+N' FROM '+@tbname+N' '+@Where+N' '+@FieldOrder);
ELSE
BEGIN
    SELECT @PageCurrent=@TopN1,
           @sql=N'SELECT @n=@n-1,@s=CASE WHEN @n<'+@TopN+N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@FieldKey+N' AS VARCHAR(8000))),N'''''''') ELSE N'''' END FROM '+@tbname+N' '+@Where+N' '+@FieldOrder;
    SET ROWCOUNT @PageCurrent;
    EXEC sp_executesql @sql, N'@n INT,@s NVARCHAR(4000) OUTPUT', @PageCurrent, @sql OUTPUT;
    SET ROWCOUNT 0;
    IF @sql=N''
        EXEC(N'SELECT TOP 0 '+@FieldShow+N' FROM '+@tbname);
    ELSE
    BEGIN
        SET @sql=STUFF(@sql,1,1,N'');
        EXEC(N'SELECT TOP '+@TopN+N' '+@FieldShow+N' FROM '+@tbname+N' WHERE '+@FieldKey+N' IN('+@sql+N') '+@FieldOrder);
    END
END

使用範例

DECLARE @PageCount INT;
EXEC sp_PageView 'Orders', 'OrderID', 3, 20, 'OrderID,CustomerID,OrderDate', 'OrderDate DESC', 'CustomerID=''ALFKI''', @PageCount OUTPUT;
SELECT @PageCount AS TotalPages;