Stored procedure implementation (with query conditions/Universal paging/General) _mssql

Suppose there is a table in the database, the table name is username, the field is ID (int), name (nvarchar), age (int).
If the stored procedure is not with a query condition：

Copy Code code 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 12 universal paging stored procedures，

Copy Code code 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,--returns the total number of records, not 0 value returns
@OrderType bit,--set sort type, not 0 value descending
@strWhere varchar (1000) = '--Query criteria (note: Do not add where)
As
DECLARE @strSQL varchar (6000)--subject sentence
DECLARE @strTmp varchar (2000)--Temporary variable (error may occur if query condition is too long, 100 can be modified 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

How to use：

Copy Code code 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 is a wrong place, please criticize and make progress together.