SQL_存储过程分页

-----top分页
---exec p_page_by_2017_7_temp 20,5  --执行方法
IF OBJECT_ID (N'p_page_by_2017_7_temp_Top',N'P') IS NOT NULL
DROP PROCEDURE p_page_by_2017_7_temp;
GO
CREATE PROCEDURE p_page_by_2017_7_temp
    @pageIndex INT,
    @pageSize INT
    
WITH encryption --加密

AS
    select Top (@pageIndex) * from td_message where message_id>=(select MAX(message_id) from 
(select top (@pageIndex *(@pageSize-1)+1) message_id from td_message order by message_id) as temp)
GO
    
------row_number 函数分页
---p_page_by_2017_7_temp_row_number 15,30
IF OBJECT_ID (N'p_page_by_2017_7_temp_row_number', N'P') IS NOT NULL
    DROP procedure p_page_by_2017_7_temp_row_number;
GO
CREATE procedure p_page_by_2017_7_temp_row_number
    @startIndex int,
    @endIndex int
AS 
    select * from (select *,ROW_NUMBER() over(order by message_id desc) 
as RowNumber from td_message) as temp where temp.RowNumber between @startIndex and @endIndex
    
GO
    
    
exec p_page_by_2017_7_temp_row_number 15,30
exec p_page_by_2017_7_temp 20,5