Use [Db]go/** * * * object:storedprocedure [dbo]. [Pro_pagelist] Script date:10/23/2015 16:41:39 * * * * **/SET ansi_nulls ongoset quoted_identifier ongoalter PROCEDURE [dbo]. [Pro_pagelist] (@tblName varchar (255), --table name @strgetfields varchar ( +) ='*', --columns that need to be returned @fldName varchar (255)="', --sorted field name @pagesizeint= +, --Page Size @pageindexint=1, --page @docount bit=0,--Returns the total number of records, non-0The value returns @ordertype bit=0,--Set the sort type, non-0the value is descending @strwhere varchar ( the)="'--Query criteria (note: Do not addwhere)) asdeclare @strSQL varchar ( the) --The subject sentence declare @strTmp varchar ( the) --temporary variable declare @strOrder varchar ( -) --Sort Typeif@doCount! =0beginif@strWhere! ="' Set@strSQL ='Select COUNT (*) as Total from'+ @tblName +'where'+@strWhereElse Set@strSQL ='Select COUNT (*) as Total from'+@tblName End--The above code means that if the @docount pass over 0, the total number of executions is counted. All of the following codes are in the case of @docount to 0Elsebeginif@OrderType! =0beginSet@strTmp ='< (select min'Set@strOrder ='Order by'+ @fldName +'desc'--if @ordertype is not 0, it is important to perform descending order. EndElsebeginSet@strTmp ='> (SELECT Max' Set@strOrder ='Order by'+ @fldName +'ASC'Endif@PageIndex =1beginif@strWhere! ="' Set@strSQL ='Select Top'+ STR (@PageSize) +' '[Email protected]+' from'+ @tblName +'where'+ @strWhere +' '+@strOrderElse 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 the executionElsebegin--The following code gives @strsql the SQL code to actually executeSet@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)'+@strOrderif@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 +' '+@strOrderend end EXEC (@strSQL)
SQL Server Universal Paging stored procedures