分页存储过程

分页存储过程

2007-05-22 15:26

分页存储过程，自己把“NOT_IN”和“SET ROWCOUNT”两种方式综合了一下。 “SET ROWCOUNT”算法不变，“NOT_IN”排序时加了主键字段，这样速度提升了很多而且结果是单向唯一的，但不是双向可逆的。“SET ROWCOUNT”没有速度问题，“NOT_IN”排序加了主键字段后在30W记录时对非索引字段、非聚合字段的排序翻一页不过3秒钟，还可以吧：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 + ' ' END ELSE BEGIN      SET @strSimpleFilter = ' '      SET @strFilter = ' ' END IF @Group IS NOT NULL AND @Group != '' SET @strGroup = ' GROUP BY ' + @Group + ' ' ELSE begin 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 = ' Desc ' END ELSE BEGIN      SET @operator = '>='      SET @strAsc_Des = ' ASC ' END IF CHARINDEX('.', @PK) > 0 BEGIN      SET @strPKColumn = SUBSTRING(@PK, 0, CHARINDEX('.',@PK))   END ELSE BEGIN      SET @strPKColumn = @PK   END IF CHARINDEX('.', @strSortColumn) > 0 BEGIN      SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))      SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn)) END ELSE BEGIN      SET @SortTable = @Tables      SET @SortName = @strSortColumn END /*Handler complex table*/ --Be join table,so get the left table IF CHARINDEX(' join ',@Tables) > 0 BEGIN SET @SortTable=SUBSTRING(ltrim(@Tables),0,CHARINDEX(' ',@Tables)) SET @strSortColumn = @SortTable+'.'+@SortName END /*Check the sortColumn if be Unique*/ DECLARE @tempName varchar(100) IF @SortName <> @strPKColumn begin SELECT @tempName=b.name FROM 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.colid WHERE (b.xtype = 'UQ') AND (a.name = @SortTable) AND (e.name = @SortName) IF @tempName IS NULL GOTO:Paging_Not_In end DECLARE @type varchar(100) DECLARE @prec int SELECT @type=t.name, @prec=c.prec FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype WHERE o.name = @SortTable AND c.name = @SortName /*The left table doesn't contain sortcolumn */ --IF     @type is null or @type=''     Goto:Paging_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 paging 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 int SET @strTotalNum = (@PageNumber - 1)*@PageSize --第一页 --Declare @strSQL varchar(8000) IF     @strTotalNum = 0 begin exec('select top '+@PageSize+' '+@Fields+' from '+@Tables+' '+@strFilter+ @strGroup + ' ORDER BY ' + @Sort + @strAsc_Des)   end else begin exec('select top '+@PageSize+' '+@Fields+' from '+@Tables+'     where ' +@PK+' not in(select top ' +@strTotalNum+' '+@PK+' from '+@Tables+' '+@strFilter+ @strGroup + ' ORDER BY ' + @Sort + @strAsc_Des +') '+@strSimpleFilter+ @strGroup + ' ORDER BY ' + @Sort + @strAsc_Des) end GO