sql分頁存儲過程，帶求和、排序 - stjwy @ IT工程師數位筆記本

文章出處

創建存儲過程：

 
CREATE PROCEDURE [dbo].[sp_TBTest_Query]
    ( 
        @PageSize INT,                        --每頁多少條記錄
        @PageIndex INT = 1,                    --指定當前為第幾頁
        @PageCount INT    OUTPUT,                --返回總頁數
        @RecordCount INT OUTPUT,            --記錄總和

        @OrderField VARCHAR(5000),            --排序字段(多字段，不含Order By)
        @strWhere VARCHAR(5000),            --條件語句(不用加where)

        @SumColumnNames NVARCHAR(200),        --匯總字段
        @SumResult NVARCHAR(200) OUTPUT        --返回匯總結果
    )
AS
    BEGIN
        DECLARE @SQL NVARCHAR(MAX) = '';
        --條件處理
        IF ISNULL(@strWhere, '') =''
        BEGIN
          SET @strWhere='1=1 '
        end
         
         
        --計算總記錄數
        SET @SQL = N'SELECT @A = count(*) FROM TBTest where '+ @strWhere
        EXEC sp_executesql @SQL, N'@A int OUTPUT', @RecordCount OUTPUT;

        --計算總頁數
        SELECT  @PageCount = CEILING(( @RecordCount + 0.0 ) / @PageSize);
        
        --處理頁數超出范圍情況
        IF @PageIndex <= 0
            SET @PageIndex = 1;
    
        IF @PageIndex > @PageCount
            SET @PageIndex = @PageCount;
            
         --處理開始點和結束點
        DECLARE @StartRecord INT;
        DECLARE @EndRecord INT;
    
        SET @StartRecord = ( @PageIndex - 1 ) * @PageSize + 1;
        SET @EndRecord = @StartRecord + @PageSize - 1;

        --計算匯總
        IF ISNULL(@SumColumnNames, '') <> ''
            BEGIN
                SET @SQL = N'SELECT @A = (CAST(ISNULL(SUM('
                    + REPLACE(@SumColumnNames, ',','), 0) AS NVARCHAR(30)) + '','' + CAST(ISNULL(SUM(')
                    + '), 0) AS NVARCHAR(30))) FROM TBTest where '+@strWhere +' '
                EXEC sp_executesql @SQL, N'@A NVARCHAR(200) OUTPUT', @SumResult OUTPUT;
            END 
             
         
        --返回結果
       SET @sql=N' SELECT * FROM (  SELECT  * ,ROW_NUMBER() OVER(ORDER BY id DESC) AS RowID
        FROM    TBTest where '+@strWhere +N') t
        WHERE   RowID BETWEEN '+CONVERT(NVARCHAR(10),@StartRecord)+N' AND '+CONVERT(NVARCHAR(10),@EndRecord)
        EXEC sp_executesql @SQL
    END

調用代碼

     DECLARE    @PageSize INT=10,                        --每頁多少條記錄
        @PageIndex INT = 2,                    --指定當前為第幾頁
        @PageCount INT     ,                --返回總頁數
        @RecordCount INT  ,            --記錄總和

        @OrderField VARCHAR(5000)='ID',            --排序字段(多字段，不含Order By)
        @strWhere VARCHAR(5000)='',            --條件語句(不用加where)

        @SumColumnNames NVARCHAR(200)='Age',        --匯總字段
        @SumResult NVARCHAR(200)          --返回匯總結果


EXEC   sp_TBTest_Query  @PageSize,@PageIndex,@PageCount OUTPUT,@RecordCount OUTPUT,@OrderField,@strWhere,@SumColumnNames,@SumResult OUTPUT

SELECT @PageCount AS 'PageCount',@RecordCount AS 'RecordCount',@SumResult AS 'SUM'

測試結果

我在項目中遇到一個問題，就是元數據由分組，然后再連接，為了加快查詢效率，我不得不剔除傳入Where條件字符串中的一個條件，大概寫法如下：

DECLARE @strWhere NVARCHAR(max)='ID=1 and Age=2 and address=''銷售部'''
DECLARE @str1 NVARCHAR(max)=''
DECLARE @str2 NVARCHAR(max)=''
IF ISNULL(@strWhere, '')<>''
BEGIN
IF( CHARINDEX('Age',@strWhere)>0 )
BEGIN
SET @str1 =SUBSTRING(@strWhere,CHARINDEX('Age',@strWhere),LEN(@strWhere))
if CHARINDEX('and',@str1)>0
begin
SET @str2 =SUBSTRING(@str1,CHARINDEX('and',@str1),LEN(@str1))
end

SET @strWhere=SUBSTRING(@strWhere,0,CHARINDEX('Age',@strWhere))+N' 1=1 '+@str2

end

END

文章列表