A paging storage process with good scalability

This is a frequently used paging storage process. I hope you can give me some advice.
Copy codeThe Code is as follows:
USE [a6756475746]
GO
/***** Object: StoredProcedure [dbo]. [tbl_order_SearchWhereAndPage] Script Date: 11/01/2011 09:37:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter procedure [dbo]. [tbl_order_SearchWhereAndPage]
@ AllCount int OUTPUT,
@ PageIndex int,
@ PageSize int,
@ MinDate datetime,
@ MaxDate datetime
AS
Begin
DECLARE @ PageLower int
Set @ PageLower = @ PageSize * @ PageIndex
DECLARE @ PageUpper int
Set @ PageUpper = @ PageLower + @ PageSize-1

DECLARE @ SearchSQL nvarchar (4000)
Set @ SearchSQL = 'select *, (ROW_NUMBER () OVER (order by [ID] DESC)-1) AS RowNumber FROM tbl_order WHERE (1 = 1 )'
DECLARE @ SearchSQLCount nvarchar (4000)
Set @ SearchSQLCount = 'select @ count = Count (*) FROM tbl_order WHERE (1 = 1 )'
Declare @ Result [varchar] (5000)
Set @ Result =''

If @ minDate> convert (datetime, '2017-1-2 ')
Begin
Set @ Result = @ Result + 'and oDeliveryDate> = ''' + convert (varchar (20), @ minDate) + ''''
End
If @ maxDate> convert (datetime, '2017-1-2 ')
Begin
Set @ Result = @ Result + 'and oDeliveryDate <= ''' + convert (varchar (20), dateadd (dd, 1, @ maxDate) + ''''
End
Set @ SearchSQLCount = @ SearchSQLCount + @ Result
Set @ SearchSQL = @ SearchSQL + @ Result

SET @ SearchSQL = 'WITH t AS (' + @ SearchSQL + ')
SELECT * FROM t
WHERE [RowNumber] between' + convert (varchar (50), @ PageLower) + 'AND' + convert (varchar (50), @ PageUpper) +'
Order by RowNumber'

Exec (@ SearchSQL)

Exec sp_executesql @ SearchSQLCount, n' @ count as int out', @ AllCount out

Print @ SearchSQL

Print @ AllCount
End
Exec (@ Result)