Generic paging stored Procedure functions

Stored Procedures | paging | functions | stored procedures | pagination | functions

ALTER PROCEDURE Pagination
(
@tblName varchar (255),--table name
@strGetFields varchar (1000) = ' * ',--columns to be returned
@fldName varchar (255) = ',--sorted field name
@PageSize int = 10,--page size
@PageIndex int = 1,--page number
@doCount bit = 0--Returns the total number of records, not 0 values.
@OrderType bit = 0,--set sort type, not 0 value descending
@strWhere varchar (1500) = '--Query criteria (note: Do not add where)
)
As

/* Define Variable * *
DECLARE @strSQL varchar (5000)--subject sentence
DECLARE @strTmp varchar (110)--Temporary variable
DECLARE @strOrder varchar (400)--Sort type

If @doCount!= 0
Begin
If @strWhere!= '
Set @strSQL = ' SELECT count (*) as total from [' + @tblName + '] where ' + @strWhere
Else
Set @strSQL = ' SELECT count (*) as total from [' + @tblName + '] '
End
--The above code means that if @docount passes over 0, the total count is executed. All of the following code is @docount 0
Else
Begin
If @OrderType!= 0--if @ordertype is not 0, perform descending order, which is important!
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

If @PageIndex = 1
Begin
If @strWhere!= '
Set @strSQL = ' SELECT top ' + str (@PageSize) + ' + @strGetFields + ' from [' + @tblName + '] where ' + @strWhere + ' + @s Trorder
Else
Set @strSQL = ' SELECT top ' + str (@PageSize) + ' + @strGetFields + ' from [' + @tblName + '] ' + @strOrder
--If the first page executes the above code, this will speed up execution
End
Else
Begin
--The following code gives @strsql the SQL code to actually execute
Set @strSQL = ' SELECT top ' + str (@PageSize) + ' + @strGetFields + ' from [' + @tblName + '] where [' + @fldName + '] ' + @st RTMP + ' ([' + @fldName + ']) from (select Top + str (@PageIndex-1) * @PageSize) + ' [' + @fldName + '] from [' + @tblName + '] ' + @strOrder + ') as tbltmp) ' + @strOrder

If @strWhere!= '
Set @strSQL = ' SELECT top ' + str (@PageSize) + ' + @strGetFields + ' from [' + @tblName + '] where [' + @fldName + '] ' + @s Trtmp + ' ([' + @fldName + ']) from (select Top + str (@PageIndex-1) * @PageSize) + ' [' + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' + @strOrder + ') as tbltmp) and ' + @strWhere + ' + @strOrder
End
End

EXEC (@strSQL)