A common paging Stored Procedure Code Based on ROW_NUMBER ()

After the following stored procedure is created, you can directly call the stored procedure by page.
Note: If the data volume is large and the performance requirement is high, please customize the processing.
Copy codeThe Code is as follows:
Alter procedure [dbo]. [COMMON_PROCEDURE_SelectWithPage]
@ SQL VARCHAR (5000 ),
@ CurrentPageNo INT,
@ PageSize INT,
@ TotalNum INT OUTPUT
AS
SET NOCOUNT ON
DECLARE @ SqlCmd VARCHAR (5000)
---------------------------------------- -- Query data
SET @ SqlCmd = 'select * FROM ('+ @ SQL +') A WHERE RowIndex BETWEEN '+ CONVERT (VARCHAR, (@ CurrentPageNo-1) * @ PageSize + 1) + 'and' + CONVERT (VARCHAR, @ CurrentPageNo * @ PageSize)
EXEC (@ SqlCmd) PRINT (@ SqlCmd)
---------------------------------------- -- Calculate the total number of records
IF @ TotalNum =-1
BEGIN
Create table # Temp1 (num INT)
Insert into # Temp1
EXEC ('select count (*) FROM ('+ @ SQL +') ')
SELECT @ TotalNum = (SELECT * FROM # Temp1)
Drop table # Temp1
END

The usage is simple, but ROW_NUMBER () OVER (...) AS RowIndex must be used in the passed SQL statement:
DECLARE @ SQL VARCHAR (5000)
DECLARE @ CurrentPageNo INT
DECLARE @ PageSize INT
DECLARE @ TotalNum INT

SET @ CurrentPageNo = 100
SET @ PageSize = 10
SET @ TotalNum =-1
SET @ SQL = 'select *, ROW_NUMBER () OVER (ORDER BY sorting field) AS RowIndex FROM table name A WITH (NOLOCK )'

EXEC [dbo]. [COMMON_PROCEDURE_SelectWithPage] @ SQL, @ CurrentPageNo, @ PageSize, @ TotalNum OUTPUT

SELECT @ TotalNum