*
Function Description: General Package Display Query
Condition: There is no indicator increment field in the table
Input parameters:
@TBLNAME: Table name
@StrGetfields: Returned column '*': Return to list information
@fldname: Sort field name
@ORDERTYPE: Setting the sort type, descending in a non-0 value
@PageSize: page size
@PageIndex: page number
@docount: Return to the total number of records, non-0 values return
@strorderby: Default Sort Field Information (Note: Don't add ORDER BY)
Format: Field1 DESC, Field2 ASC,
@Strwhere: Query Conditions (Note: Don't add where)
Output parameter: @Recordcount: Total number of records
Author: Ningfeiyang
Creation time: 2005-01-19
Change record:
* /
Alter Procedure Paging2
(
@tblname varchar (255),
@StrGetfields VARCHAR (1000) = '*',
@fldname varchar (255) = '',
@PageSize Int = 10,
@PageIndex INT = 1,
@docount bit = 0,
@ORDERTYPE BIT = 0,
@strorderby varchar (500) = '',
@Strwhere Varchar (1500) = '',
@Recordcount Int Output
)
AS
Declare @strsql varchar (5000) - main statement
Declare @strtmp varchar (200) - Temporary variable
Declare @strorder varchar (400) - Sort variable
- If @docount passed is not 0, execute the total number of statistics if (@docount! = 0) Begin Declare @Swhere Varchar (2000) set @Swhere = '' IF (@strwhere! = ') Set @Swhere = 'Where' @Strwhere set @strsql = 'if exists (select * from dbo.sysObjects where id = Object_id (' '[dbo]. [Tmptable]') And ObjectProperty (id, '' isusertable ') = 1 ) 'Set @strsql = @strsql ' update tmptable set total = (select count (*) from [' @TBLNAME '] ' @Swhere ') 'set @strsql = @strsql ' Else Select Count ( *) As Total InTo Tmptable from [' @TBLNAME '] ' @Swhere Exec (@STRSQL) Select @ recordcount = Total from TMPTABLE - Delete Total Statistics Temporary Table EXEC (' DROP TABLE TMPTABLE ') End --Print @RecordCount - If @RDertype is not 0, execute descending IF (@ordertype! = 0) begin set @strtmp = '<(select min' set @strorder = 'order by' @strorderby '[' @fldname '] Desc' end else begin set @strtmp = '> (select max' set @strorder = 'order by' @strorderby '[' @fldname '] ASC' END
- If you are the first page, you will implement the above code, which will speed up the speed 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 END ELSE BEGIN - Save the Auto Number for Search Table Save to Temporary Table Set @strsql = 'SELECT TOP' STR (@ pageindex * @ Pagesize) 'Identity (int, 1, 1 ) AS IID, ' @StrGetfields ' Into #tmptable from [' @TBLNAME '] 'IF @Strwhere! =' 'Set @strsql = @STRSQL ' WHERE ' @Strword Else Set @strsql = @strsql @strorder - The following code gives @STRSQL to real execution SQL code set @strsql = @strsql 'select top' str (@Pagesize) '' @StrGetfields 'from # # # TMPTABLE ' ' WHERE IID ' @strtmp ' (IID) from (SELECT TOP ' STR ((@ PageIndex-1) * @ PageSize) ' Iid from #tmptable) AS TBLTMP) DROP TABLE #TMPTABLE 'END - -Print @ strsql - Execute a paging query exec (@strsql) check article: http: //dev.9cbs.net/develop/Article/4 5 / 45356. SHTM friends have any good opinions can be improved, thank you!
