Paging data and get row count with out considering the page number in stored procedure

Question

I am using SQL Server 2012. I need to do paging in my stored procedure. In client side (Web page), I need to have the result of the stored procedure for requested page and also I need to have the row count without considering the page number for setting number of page with that condition.

For example, I need to run this stored procedure for querying ten row of second page when my SubscribeId is 12345674.

Create Procedure TestSelectBill  
    (@PageNumber int = 1 ,  
     @RowCount int = 10  ,
     @SubscribeId Int = 0)  
As  
Begin  
    Select *  
    From billing.BillMaster As BM  
    Where (Bm.SubscribeId = @SubscribeId)  
    Order by SubscribeId  
        Offset (@PageNumber - 1) * @RowCount Rows 
        Fetch Next @RowCount Rows Only;  
End  

I have to execute this stored procedure like this :

Execute TestSelectBill  
        @PageNumber = 2, @RowCount int = 10, @SubscribeId = 12345674

Imagine, that I have 105 rows in billing.BillMaster for this SubscribeId = 123456574. Now I need to show 10 row to my end user as result and I have to let him to select one page between 1 to 11.

That means I need to know how many row is exist for this condition SubscribeId = 123456574.

I can change my stored procedure like the below code to return row count:

Create Procedure TestSelectBill  
    (@PageNumber int = 1,  
     @RowCount int = 10,
     @SubscribeId Int = 0)  
As  
Begin  
    DECLARE @ROW_COUNT INT = 0

    -- Find Row Count for this condition
    Select   
        @ROW_COUNT = COUNT(*)  
    From 
        billing.BillMaster As BM  
    Where  
        (Bm.SubscribeId = @SubscribeId)  

    -- Select Result
    SELECT
        Row_Count = @ROW_COUNT,    
        *  
    FROM
        billing.BillMaster As BM  
    WHERE
        (Bm.SubscribeId = @SubscribeId)  
    ORDER BY
        SubscribeId  
        OFFSET ( @PageNumber - 1 ) * @RowCount ROWS  
        FETCH NEXT @RowCount ROWS ONLY;  
End  

But as you see I have to write my select two times and it is not good because modification and maintenance of this stored procedure will be very complicated.

Also, I can save my result into temp table and then use that like the below code:

CREATE Procedure TestSelectBill  
    (@PageNumber int = 1,  
     @RowCount int = 10,
     @SubscribeId Int = 0)  
As  
Begin  
    DECLARE @ROW_COUNT INT = 0

    -- Main Select 
    SELECT                      
        *  
    FROM 
        billing.BillMaster As BM  
    INTO 
        #T
    WHERE
        (Bm.SubscribeId = @SubscribeId)  

    -- Find Row Count for this condituion
    SELECT @ROW_COUNT = COUNT(*)  
    FROM #T

    -- Select Result
    SELECT
        Row_Count = @ROW_COUNT, 
        *  
    FROM
        #T
    ORDER BY 
        SubscribeId  
        OFFSET (@PageNumber - 1) * @RowCount ROWS  
        FETCH NEXT @RowCount ROWS ONLY;  
End  

But as you can see in this way, I am using physical temp table that can be very slow when I have a lot of data in main select with out paging.

Can anyone tell me the best way to do that?

Answer 1

-- First solution use count with window function
CREATE Procedure TestSelectBill  
    (@PageNumber int = 1,  
     @RowCount int = 10,
     @SubscribeId Int = 0)  
As  
Begin  
    SELECT
        COUNT(*) OVER(ORDER BY (SELECT NULL)) AS row_count ,
        *  
    FROM
        billing.BillMaster As BM  
    WHERE
        (Bm.SubscribeId = @SubscribeId)  
    ORDER BY
        SubscribeId  
        OFFSET (@PageNumber - 1) * @RowCount ROWS  
        FETCH NEXT @RowCount ROWS ONLY;  
End  
GO


-- Second solution: use dynamic sql with multiple result
Create Procedure TestSelectBill  
     @PageNumber int = 1,  
     @RowCount int = 10,
     @SubscribeId Int = 0
As  
Begin  

    DECLARE @params NVARCHAR(max) = '@PageNumber int, @RowCount int, @SubscribeId int'
    DECLARE @where NVARCHAR(max) = N' WHERE Bm.SubscribeId = @SubscribeId'
    DECLARE @stmt NVARCHAR(max) = N'SELECT COUNT(*) as row_cnt FROM billing.BillMaster As BM '
    DECLARE @stmt_rowcount NVARCHAR(max) = N'SELECT * FROM billing.BillMaster As BM '
    DECLARE @order_by NVARCHAR(max) = ' ORDER BY SubscribeId  
        OFFSET (@PageNumber - 1) * @RowCount ROWS  
        FETCH NEXT @RowCount ROWS ONLY;'

    SET @stmt += @where + @order_by
    SET @stmt_rowcount += @where

    -- First result set (rowcount)
    EXEC [sys].[sp_executesql]
        @stmt = @stmt_rowcount,
        @params = @params,
        @SubscribeId = @SubscribeId,
        @PageNumber = @PageNumber,
        @RowCount = @RowCount


    -- Second result set (data)
    IF @@ERROR = 0
    BEGIN
        EXEC [sys].[sp_executesql]
            @stmt = @stmt,
            @params = @params,
            @SubscribeId = @SubscribeId,
            @PageNumber = @PageNumber,
            @RowCount = @RowCount

    END

End  
GO

Answer 2

You could create a temp table with an identity column that increments by 1 for each row inserted, then read the max value of that column to get the row count.