Stored Procedure Implementation (with query conditions, universal paging, and General)

Assume that there is a table in the database. The table Name is UserName, and the fields are ID (int), Name (nvarchar), and Age (int ).
If the stored procedure without query conditions is:
Copy codeThe Code is as follows:
Create procedure [dbo]. [UserName]
@ PageIndex int,
@ PageSize int
AS
Declare @ min int;
Declare @ max int;
Set @ min = @ pageSize * (@ pageIndex-1) + 1;
Set @ max = @ pageSize * @ pageIndex;
With myTable as (select ID, Name, Age, Row_Number () over (order by ID) as rownum from [UserName])
Select ID, Name, Age from myTable where rownum between @ min and @ max
RETURN

This paging stored procedure is not practical and the table is fixed.
The following 0.12 million paging stored procedures,
Copy codeThe Code is as follows:
Create procedure [dbo]. [UP_GetRecordByPage]
@ TblName varchar (255), -- table name
@ FldName varchar (255), -- primary key field name
@ PageSize int, -- page size
@ PageIndex int, -- page number
@ IsReCount bit, -- total number of records returned. If the value is not 0
@ OrderType bit, -- set the sorting type. If the value is not 0, the sorting type is descending.
@ StrWhere varchar (1000) = ''-- Query condition (Note: Do not add where)
AS
Declare @ strSQL varchar (6000) -- subject sentence
Declare @ strTmp varchar (2000) -- temporary variable (an error may occur when the query condition is too long. You can change 100 to 1000)
Declare @ strOrder varchar (400) -- sort type
If @ OrderType! = 0
Begin
Set @ strTmp = '<(select min'
Set @ strOrder = 'order by ['+ @ fldName +'] desc'
End
Else
Begin
Set @ strTmp = '> (select max'
Set @ strOrder = 'order by ['+ @ fldName +'] asc'
End
Set @ strSQL = 'select top '+ str (@ PageSize) +' * from ['
+ @ TblName + '] where [' + @ fldName + ']' + @ strTmp + '(['
+ @ FldName + ']) from (select top' + str (@ PageIndex-1) * @ PageSize) + '['
+ @ FldName + '] from [' + @ tblName + ']' + @ strOrder + ') as tblTmp )'
+ @ StrOrder
If @ strWhere! =''
Set @ strSQL = 'select top '+ str (@ PageSize) +' * from ['
+ @ TblName + '] where [' + @ fldName + ']' + @ strTmp + '(['
+ @ FldName + ']) from (select top' + str (@ PageIndex-1) * @ PageSize) + '['
+ @ FldName + '] from [' + @ tblName + '] where' + @ strWhere +''
+ @ StrOrder + ') as tblTmp) and' + @ strWhere + ''+ @ strOrder
If @ PageIndex <> 0
Begin
Set @ strTmp =''
If @ strWhere! =''
Set @ strTmp = 'where' + @ strWhere
Set @ strSQL = 'select top '+ str (@ PageSize) +' * from ['
+ @ TblName + ']' + @ strTmp + ''+ @ strOrder
Exec (@ strSQL)
End
If @ IsReCount! = 0
Begin
Set @ strSQL = 'select count (*) as Total from ['+ @ tblName +'] '+ 'where' + @ strWhere
Exec (@ strSQL)
End

Usage:
Copy codeThe Code is as follows:
EXEC dbo. UP_GetRecordByPage @ tblName = 'username', -- varchar (255)
@ FldName = 'id', -- varchar (255)
@ PageSize = 2, -- int
@ PageIndex = 1, -- int
@ IsReCount = 0, -- bit
@ OrderType = 1, -- bit
@ StrWhere = 'Age = 13' -- varchar (1000)

If there are any errors, you are welcome to criticize and correct them and make progress together.