Alter Procedure Pager (@tblname varchar (255), - Table name @StrGetfields varchar (1000) = '*', - Need to return @fldname varchar (255) = '', - Sort field name @PageSize INT = 10, - page size @PageIndex INT = 1, - Type @ORDERTYPE Bit = 0, - Set Sorting Type, Non 0 Value Design @Strwhere Varchar (1500) = '- Query Conditions (Note: Don't add where)) asclare @strsql varchar (5000) - primary sentence DECLARE @strtmp varchar (110) - Temporary Variable Declare @strORDER VARCHAR (400) - Sort Type IF @ORDERTYPE! = 0 Begin Set @Strtmp = ' <(Select Min 'Set @strorder =' Order By [' @fldname '] desc '- If @ordertype is not 0, it will be designed, this sentence is very important! 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 '' @strorder else set @strsql = 'select top' str (@Pagesize) '' @ strGetfields 'from [' @TBLNAME ']' @strorder - if it is the first page Execute the above code, so that the speed END ELSE BEGIN - the following code gives @strsql to really executed SQL code set @strsql = 'select top' str (@PageSize '" @ strGetfields ' 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) ' ' @ strGetfields ' 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 EXEC (@STRSQL) - Statistics Total number if @Strwhere! = '' Set @strsql = 'select count (*) from [' @tblname '] where' @strwhereelse set @strsql = 'select count (*) from [' @TBLName ']' EXEC (@strsql)
