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
Declare @strfilter varchar (1000) Declare @strsimplefilter varchar (1000) Declare @strgroup varchar (1000)
Declare @sorttable varchar (100) Declare @SortName Varchar (100) Declare @strpkcolumn varchar (100)
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' @Group '' ELSEbegin SET @strGroup = '' end
/ * count * / exec ('select count (*) from' @ Tables '' @strfilter)
/ * Operator and asc_desc * / declare @strasc_des varchar (10) if @isascending = 0 begin set @operator = '<=' set @strasc_des = 'desc' endelse begin set @operator = '> =' set @strasc_des = ' ASC 'end / * set sorting variables. * // * default sorting * / if @Sort is null @ Sort =' 'beginset @Sort = @pken
Set @STRSORTCOLUMN = @sort
If Charindex ('.', @Pk)> 0 Begin Set @strpkcolumn = Substring (@pk, charindex ('.', @ Pk) 1, len (@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
/ * Handle 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 * /
IF @Sortname <> @strpkcolumnbegindeclare @tempname varchar (100)
SELECT @ Tempname = B.NameFrom 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 begin goto Paging_not_in
Endend
Page_rowcount:
/ * Find the @Sort Type * /
Declare @Type varchar (100) Declare @PREC INT
SELECT @ type = t.name, @ prec = c.precFROM sysobjects o JOIN syscolumns c on o.id = c.idJOIN systypes t on c.xusertype = t.xusertypeWHERE o.name = @SortTable AND c.name = @SortName
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) ReturnPaging_not_in:
Declare @Strtotalnum Intset @StrtotalNum = (@PAGENUMBER - 1) * @ PageSize
- If the sort is the index column NOTIN method is very slow - lower limit at 1000 - IF (@Stotalnum> 10000) - Begin - Declare @totalcount Int - Declare @sql nvarchar (100)
--set @SQL = 'select @totalcount = count (*) from' @ Tables '' @ Strfilter - EXEC SP_EXECUTESQL @ SQL, N '@ Totalcount Int output', @ Totalcount Out - the upper limit 3/4 Tying - IF (@ stratotalnum / @ Totalcount <0.75) Goto Paging_ASCD_ESC - END
- 1 @Strtotalnum = 0 Beginexec ('SELECT TOP' @ Pagesize ' @ Fields ' from ' @ Tables ' ' @ Strfilter @StrGroup ' Order By ' @Sort @ strasc_des ', ' @PK @strAsc_Des) endELSEbeginexec (' select top ' @ PageSize ' ' @ Fields ' from ' @ Tables ' where ' @ PK ' not in (select top ' @ strTotalNum ' ' @ PK ' from ' @ Tables ' ' @ strFilter @strGroup ' ORDER BY ' @Sort @strAsc_Des ', ' @PK @ strAsc_Des ') ' @ strSimpleFilter @strGroup ' ORDER BY ' @Sort @strasc_des ',' @pk @strasc_des) endreturnpaging_ascd_esc: Go
