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?
-- 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