一個通用的分頁存儲過程實作-SqlServer（附上sql源碼，一鍵執行即刻搭建運作環境）

本文提供一種通用的分頁存儲過程，隻需要傳入：表名（以DBName.dbo.TableName）的形式 ，Where條件（ID 0 AND ID 100），Select字段（ID,NAME,CreateDate），Order字段（NAME ASC,CreateDate DESC），PageSize （15），PageIndex（2），TotalCount 此為output參數。這7個參數，存儲過程就能夠傳回指定條件下的分頁資料，和資料總數。

使用前提

　　查詢表必須有ID字段，且該字段不能重複，建議為自增主鍵

背景

　　如果使用ADO.NET進行開發，在查詢分頁資料的時候一般都是使用分頁存儲過程來實作的，本文提供一種通用的分頁存儲過程，隻需要傳入：

1. 表名（以DBName.dbo.TableName）的形式 
2. Where條件（ID > 0 AND ID < 100）
3. Select字段（ID,NAME,CreateDate）
4. Order字段（NAME ASC,CreateDate DESC）
5. PageSize （15）
6. PageIndex（2）
7. TotalCount，此為output參數

　　這7個參數，存儲過程就能夠傳回指定條件下的分頁資料，和資料總數。

sql源碼&測試環境搭建

--建立Util庫
CREATE DATABASE Util
GO

--建立通用的分頁存儲過程
USE Util
GO
/****** Object:  StoredProcedure [dbo].[UP_GeneralPagedQuery_v1]    Script Date: 04/03/2014 17:32:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Author:        DeanZhou
-- Create date: 2013-09-24
-- Description:    通用的分頁存儲過程（一）
CREATE PROCEDURE [dbo].[UP_GeneralPagedQuery_v1]
    @TableName VARCHAR(100) ,                    --表名稱：如 MKT.dbo.UV_CouponInfo
    @WhereField NVARCHAR(1000) = '' ,            --篩選條件：如 Status = 1 AND CreateUser = 'admin'
    @SelectField NVARCHAR(1500) = '*' ,          --需要查詢的列：如 *
    @OrderField NVARCHAR(1000) = '' ,            --需要進行排序的字段：如  CustomerName desc,StartDate asc,Status desc,id asc 
    @PageSize INT = 15 ,                         --頁面大小：如 15
    @PageIndex INT = 1 ,                         --目前頁面：如 1
    @TotalCount INT = 0 OUT                      --記錄總數：輸出值
AS 
    BEGIN   

        IF @OrderField IS NULL OR @OrderField = '' 
            BEGIN
                SET @OrderField = ' ID '
            END

        IF @WhereField IS NULL OR @WhereField = '' 
            BEGIN
                SET @WhereField = ' WHERE ID > 0 '
            END
        ELSE 
            BEGIN
                SET @WhereField = ' WHERE ' + @WhereField
            END
            
        DECLARE @ExceptCount INT = @PageSize * ( @PageIndex - 1 )
        DECLARE @TakeCount INT = @PageSize    
        DECLARE @IsNeedSubQuery INT
        
        DECLARE @SqlPreview NVARCHAR(MAX) = 
              'SELECT @C = COUNT(1) FROM ' + @TableName + ' ' + @WhereField + ';'
            + 'IF @EC < 0 SET @EC = 0 IF @EC >= @C SET @EC = @C;'
            + 'IF @TC < 0 SET @TC = 15 IF (@EC + @TC) > @C SET @TC = @C - @EC;'
            + 'IF @EC > 0 AND @TC > 0 SET @NSQ = 1 ELSE SET @NSQ = 0;'
                                             
        EXEC sp_executesql @SqlPreview,
            N'@C INT OUTPUT,@EC INT OUTPUT,@TC INT OUTPUT,@NSQ INT OUTPUT',
            @TotalCount OUTPUT, @ExceptCount OUTPUT, @TakeCount OUTPUT, @IsNeedSubQuery OUTPUT    
            
        DECLARE @MaxOrMin VARCHAR(3) = 'MAX'
        DECLARE @DescOrAsc VARCHAR(4) = ''
        IF @ExceptCount > @TotalCount / 2 
            BEGIN
                SET @MaxOrMin = 'MIN'
                SET @DescOrAsc = 'DESC'
                SET @ExceptCount = @TotalCount - @ExceptCount + 1
            END

        DECLARE @SqlQuery NVARCHAR(MAX) = 
              ' DECLARE @T_IDS TABLE (ID INT) '
                + ' IF @NSQ = 1' + 
                    ' BEGIN ' + 
                    '    SELECT @MD = ' + @MaxOrMin + '(ID) FROM '+
                    '        (SELECT TOP ' + CONVERT(VARCHAR(15), @ExceptCount) + ' ID FROM ' + @TableName + ' ' + @WhereField + ' ORDER BY ' + @OrderField + ')T1;' + 
                    '    INSERT INTO @T_IDS '+
                    '         SELECT TOP ' + CONVERT(VARCHAR(15), @TakeCount) + ' ID FROM ' + @TableName + ' ' + @WhereField + ' AND ID > @MD  ORDER BY ' + @OrderField + 
                    '    SELECT ' + @SelectField + ' FROM ' + @TableName + ' S WHERE ID IN (SELECT ID FROM @T_IDS) ORDER BY ' + @OrderField + 
                    ' END ' + 
                  ' ELSE' + 
                    ' BEGIN ' + 
                    '    INSERT INTO @T_IDS SELECT ID FROM (SELECT TOP ' + CONVERT(VARCHAR(15), @TakeCount) + ' ID FROM ' + @TableName + ' ' + @WhereField + ' ORDER BY  ' + @OrderField + ')T;' + 
                    '    SELECT ' + @SelectField + ' FROM ' + @TableName + ' S WHERE ID IN (SELECT ID FROM @T_IDS) ORDER BY ' + @OrderField + 
                    ' END '
                                          
        EXEC sp_executesql @SqlQuery, N'@MD INT,@NSQ INT', 0, @IsNeedSubQuery
        
    END   
    
    GO
    
--建立測試庫
CREATE DATABASE Test
GO

--建立測試表
USE [Test]
CREATE TABLE TableTest (ID INT,NAME NVARCHAR(50),CreateDate DATETIME)
GO

--插入測試資料
INSERT INTO TableTest
SELECT 1,'dean1',GETDATE() UNION
SELECT 2,'dean2',GETDATE() UNION
SELECT 3,'dean3',GETDATE() UNION
SELECT 4,'dean4',GETDATE()       

　　打開您的sqlserver，在本地建立查詢，并運作上面的代碼，會在你的資料庫中建立以下内容：

1. 一個名稱為【Util】的資料庫，該庫下面有一個名為【UP_GeneralPagedQuery_v1】的存儲過程，這個存儲過程就是通用的分頁存儲過程。
2. 一個名稱為【Test】的資料庫，該庫下面有一個名為【TableTest】的表，這個表裡面有4條資料

請注意：在執行sql之前确認一下沒有重名資料庫，以免出錯

 測試

　　建立一個查詢，執行下面sql，就完成了分頁資料的擷取

DECLARE    @TotalCount int
EXEC Util.[dbo].[UP_GeneralPagedQuery_v1]
        @TableName = N'Test.dbo.TableTest',
        @WhereField = N'ID > 0 AND ID < 100',
        @SelectField = N'ID,NAME,CreateDate',
        @OrderField = N'NAME ASC,CreateDate DESC',
        @PageSize = 2,
        @PageIndex = 2,
        @TotalCount = @TotalCount OUTPUT

SELECT    @TotalCount as N'@TotalCount'