The paging store procedure, integrates "not_in" and "set rowcount". The "set rowcount" algorithm is unchanged, "not_in" plus the primary key field, so that the speed has increased a lot and the result is unidirectional, but is not two-way reversible.
"Set rowcount" does not have a speed problem, "not_in" sorts the non-index field when the primary key field is added to the non-index field, and the non-aggregated field is shown by the page for 3 seconds, OK: P ------ ---------------- Create Procedure Paging_custom (@tables varchar (1000), @ pk varchar (100), @ Sort varchar (200) = null, @ isascending bit = 1, @ Pagenumber int = 1, @ pagesize int = 10, @ Fields varchar (1000) = '*', @filter varchar (1000) = null, @ group varchar (1000) = null) AS / * DEFAULT SORTING * / IF @Sort IS NULL OR @Sort = '' SET @Sort = @PK / * Find the @SORT type * / DECLARE @strFilter varchar (1000) DECLARE @strSimpleFilter varchar (1000) DECLARE @strGroup varchar (1000) DECLARE @SortTable varchar (100 ) DECLARE @SortName varchar (100) DECLARE @strPKColumn varchar (200) DECLARE @strSortColumn varchar (200) DECLARE @operator char (2) / * Set filter & group variables. * / IF @Filter IS NOT NULL AND @Filter! = '' BEGIN SET @strFilter = 'WHERE' @Filter '' SET @strSimpleFilter = 'AND' @Filter '' ENDELSE BEGIN SET @strSimpleFilter = '' SET @strFilter = '' ENDIF @Group IS NOT NULL AND @Group! = '' Set @strgroup = 'group by' @gr OUP 'Elsebegin Set @strGroup =' 'end / * count * / exec (' select count (*) from ' @ Tables ' ' @strfilter / * set sorting variables. * / set @strsortcolumn = @
Sort / * Operator and asc_desc * / declare @strasc_des varchar (10) if @isascending = 0 begin set @operator = '<=' set @strasc_des = 'dec' endelse begin set @operator = '> =' set @strasc_des = 'ASC' Endif Charindex ('.', @Pk)> 0 Begin Set @strpkcolumn = Substring (@pk, 0, charindex ('.', @ Pk) endelse begin set @strpkcolumn = @pk endif charIndex ('. ', @strSortColumn)> 0 BEGIN SET @SortTable = SUBSTRING (@strSortColumn, 0, CHARINDEX ('. ', @ strSortColumn)) SET @SortName = SUBSTRING (@strSortColumn, CHARINDEX ('. ', @ strSortColumn) 1, LEN (@strSortColumn)) ENDELSE BEGIN SET @SortTable = @Tables SET @SortName = @strSortColumn END / * Handler complex table * / - Be join table, so get the left tableIF CHARINDEX ( 'join', @ Tables)> 0BEGINSET @ SortTable = SUBSTRING (ltrim (@Tables), 0, CHARINDEX ( '', @ Tables)) SET @strSortColumn = @SortTable '.' @ SortNameEND / * Check the sortColumn if be Unique * / DECLARE @tempName varchar (100 ) If @Sortname <> @strpkcolumnbeginselect @ TempName = B.NameFrom sysobjects a inner Join Sysobjects B on A.Id = b.parent_obj inner Join Sysindexes c on B.Name = C.Name Inner Join Sysindexkeys D on C.ID = D.ID and c.indid = D. Indid Inner Join Syscolumns E on D. ID = E.ID and d.colid = E.Colidwhere (B.XTYPE = 'uq') and (a.name = @SortTable) and (e.name = @SortName) if @tempname is null goto: paning_not_inend declare @
TYPE VARCHAR (100) Declare @Prec int select @ type = t.name, @ PREC = C.Precfrom sysobjects o join syscolumns c on o.ID = c.idjoin systemypes t on c.xUsertype = T.XUSERTYPEWHERE O.NAME = @SortTable and c.name = @SortName / * The Left Table Doesn't Contain SortColumn * / - IF @Type is null @ type = '' goto: paning_not_in if charindex ('char', @Type)> 0 set @type = @type '(' CAST (@prec AS varchar) ')' DECLARE @strPageSize varchar (50) DECLARE @strStartRow varchar (50) / * Default Page Number * / IF @PageNumber <1 SET @PageNumber = 1 / * set paning variables. * / Set @strpagesize = cast (@PageSize as varchar (50)) set @strstartrow = cast ((@ PAGENUMBER - 1) * @ PageSize 1) As varchar (50)) / * Execute dynamic query * / EXEC ( 'DECLARE @SortColumn' @type 'SET ROWCOUNT' @strStartRow 'SELECT @ SortColumn =' @strSortColumn 'FROM' @Tables @strFilter '' @strGroup 'Order by' @Sort @ strasc_des 'set rowcount' @strpagesize 'SELECT' @fields 'from' @Tables 'Where' @strsortcolumn @Operator '@SortColumn' @strSimpleFilter '' @strGroup 'ORDER BY' @Sort @strAsc_Des) return Paging_Not_In: - DECLARE @strPageSize varchar (50) - SET @strPageSize = CAST (@ PageSize As Varchar (50)) Declare @Strtotalnum Intset @
