Paging Stored Procedures

Paging Stored ProceduresCreate PROCEDURE Sp_conn_sort

(
@tblName varchar (255),--table name

@strGetFields varchar (1000) = ' * ',--the column that needs to be returned

@fldName varchar (255) = ',--Sort the field name

@PageSize int = 40,--page size

@PageIndex int = 1,--page

@doCount bit = 0,--Returns the total number of records, and a value other than 0 returns

@OrderType bit = 0,--set sort type, not 0 value descending
@strWhere varchar (1500) = "--Query criteria (note: Do not add where)
)
As

DECLARE @strSQL varchar (5000)--the 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 '[email protected]

Else

Set @strSQL = ' SELECT count (*) as total from ' + @tblName

End

--The above code means that if @docount passes over 0, the total number of executions is counted. All of the following codes are in the case of @docount to 0

Else

Begin

If @OrderType! = 0

Begin

Set @strTmp = ' < (select Min ')

Set @strOrder = ' ORDER BY ' + @fldName + ' desc '

If @ordertype is not 0, it is important to perform descending order.

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) + '[email protected]+ ' from ' + @tblName + ' where ' + @strWhere + ' + @strOrder

Else

Set @strSQL = ' SELECT top ' + str (@PageSize) + '[email protected]+ ' from ' + @tblName + ' + @strOrder

--If the above code is executed on the first page, this will speed up execution

End

Else

Begin

--The following code gives the @strsql to actually execute the SQL code

Set @strSQL = ' SELECT top ' + str (@PageSize) + '[email protected]+ ' 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) + '[email protected]+ ' from '

+ @tblName + ' where ' + @fldName + ' + @strTmp + ' ('

+ @fldName + ') from (select top ' + str (@PageIndex-1) * @PageSize) + '

+ @fldName + ' from ' + @tblName + ' where ' + @strWhere + '

+ @strOrder + ') as tbltmp) and ' + @strWhere + ' + @strOrder

End

End

EXEC (@strSQL)

-----------------

Paging Stored Procedures