/ * Function Description: Universal Page Display Query If there is an self-added identification field, do not join this field information in @StrGetFields, if you want to join, you want (fldName 0) as fldName this processing; enter parameters: @tblname: Table @StrGetfields: Returned column '*': Return to list information @Pagesize: page size @PageIndex: page @docount: Return to record total, non-0 value returns @strorderby: Sort field information, (Note: Do not add ORDER BY) Format: Field1 Desc, Field2 ASC @Strwhere: Query Condition, (Note: Do not add WHERE) Output parameters: @Recordcount: Record total number of records: Ningfeiyang creation time: 2005-01-21 Change record: * / alter procedure PAGINATION2 (@TBLNAME VARCHAR (255), @StrGetfields varchar (1000) = '*', @PageSize Int = 10, @PageIndex INT = 1, @docount bit = 0, @strorderby varchar (500) = ', @Strwhere Varchar (1500) = '', @RecordCount Int output) AS - Priority Declare @STRSQL VARCHAR (5000) SET @STRSQL = '- Sort Variable Declare @strorder Varchar (400) Set @strorder =' 'set @Recordcount = 0 - If @Docount passes 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 [@Swhere Exec (@STRSQL) Select @ recordcount = Total from Tmptable - Delete Total Statistics Temporary Table Exec ('Drop Table TMPTABLE') End print @Recordcount - Sort field information if (@strorderby! = ') Set @strorder =' order by ' @strorderby - If you are the first page, do the following code, this will 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 Else Begin - Create Auto Number for Search Table To the 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 ' @Strwhere @StrORDER ELSE set @STRSQL = @strsql @strorder - The following code gives @strsql to actually execute SQL code set @strsql = @STRSQL 'SELECT' @StrGetfield S 'from #tmptable where Iid> Str ((@ pageindex-1) * @ PageSize) ' Drop Table #TMPTABLE 'End Print @STRSQL - Execute Page Query EXEC (@strsql)
