使用一个存储过程从表中获取最后 N 行 [英] Get N last rows from table with one stored procedure

问题描述

我想用一个存储过程从表中获取最后 N 行.

I want get N last rows from table with one stored procedure.

我的存储过程有一些参数，如 @PageNumber 和 @RowCount 等.

My stored procedure has some parameters like @PageNumber and @RowCount and etc.

我有两个问题:

• 我需要在我的用户界面中计算行数和结果，因为我想限制我的用户在最后一页时按下一页.

• I need to count the rows and results in my user interface because I want to limit my user when he is in last page and press next page.

我希望我的用户在其他页面时可以按最后一页.

I want my user can press last page when he is in other page.

提示:我不想两次执行我的存储过程来获取结果和行数，因为它会创建动态并且需要很长时间来执行.

Tip: I don't want to execute my stored procedure twice to get the results and row count because it creates dynamic and need long time for execution.

对于更多描述，我最喜欢说我的 sp 就像:

For more description I most say that my sp is like :

Create Procedure TestSelectBill  
(  
    @PageNumber int = 1 ,  
    @RowCount int = 5  
)  
As  
Begin  
    Select   
        *  
    From billing.BillMaster As BM  
    Where  
    ( Bm.SubscribeId = '12345674' )  
    Order by SubscribeId  
    OFFSET @PageNumber * @RowCount ROWS  
    FETCH NEXT @RowCount ROWS ONLY;  
End  

推荐答案

这就是我的解决方案:

Select
  COUNT() OVER ( ORDER BY (SELECT NULL)) as RowNumber,
    * 
From billing.BillMaster As BM  
Where  
( Bm.SubscribeId = '12345674' )  
Order by SubscribeId  
OFFSET (@PageNumber - 1) * @RowCount ROWS  
FETCH NEXT @RowCount ROWS ONLY; 

这篇关于使用一个存储过程从表中获取最后 N 行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！