mssql千万级分页存储过程分享
文章找到了两篇关于mssql server存储过程的高效分页代码,有需要的朋友可以参考一下。
代码如下 | 复制代码 |
Create PROC P_viewPage |
自己写的一个
代码如下 | 复制代码 |
USE [CaiLi] /****** Object: StoredProcedure [dbo].[SqlPagination] Script Date: 10/26/2011 11:40:46 ******/ SET QUOTED_IDENTIFIER ON CREATE PROCEDURE [dbo].[SqlPagination] /* *************************************************************** ** 千万数量级分页存储过程 ** *************************************************************** 参数说明: 1.Tables :表名称,视图 2.PrimaryKey :主关键字 3.Sort :排序语句,不带Order By 比如:NewsID Desc,OrderRows Asc 4.CurrentPage :当前页码 5.PageSize :分页尺寸 6.Filter :过滤语句,不带Where 7.Group :Group语句,不带Group By ***************************************************************/ ( @Tables varchar(1000), @PrimaryKey varchar(100), @Sort varchar(200) = NULL, @CurrentPage int = 1, @PageSize int = 10, @Fields varchar(1000) = '*', @Filter varchar(1000) = NULL, @Group varchar(1000) = NULL AS /*默认排序*/ IF @Sort IS NULL or @Sort = '' SET @Sort = @PrimaryKey IF @Fields IS NULL or @Fields = '' SET @Fields = '*' DECLARE @SortTable varchar(100) DECLARE @SortName varchar(100) DECLARE @strSortColumn varchar(200) DECLARE @operator char(2) DECLARE @type varchar(100) DECLARE @prec int /*设定排序语句.*/ BEGIN SET @strSortColumn = REPLACE(@strSortColumn, 'DESC', '') SET @operator = ' END ELSE BEGIN IF CHARINDEX('ASC',@Sort)> 0 SET @operator = '>=' 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 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 IF CHARINDEX('char', @type) > 0 SET @type = @type + '(' + CAST(@prec AS varchar) + ')' DECLARE @strPageSize varchar(50) DECLARE @strStartRow varchar(50) DECLARE @strFilter varchar(1000) DECLARE @strSimpleFilter varchar(1000) DECLARE @strGroup varchar(1000) DECLARE @strSort varchar(200) /*默认当前页*/ IF @CurrentPage SET @CurrentPage = 1 /*设置分页参数.*/ SET @strPageSize = CAST(@PageSize AS varchar(50)) SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50)) /*筛选以及分组语句.*/ IF @Filter IS NOT NULL AND @Filter != '' BEGIN SET @strFilter = ' Where 1=1 ' + @Filter + ' ' SET @strSimpleFilter =@Filter + ' ' END ELSE BEGIN SET @strSimpleFilter = '' SET @strFilter = '' END IF @Group IS NOT NULL AND @Group != '' SET @strGroup = ' GROUP BY ' + @Group + ' ' ELSE SET @strGroup = '' IF @Sort IS NOT NULL AND @Sort != '' SET @strSort = ' ORDER BY ' + @Sort + ' ' ELSE SET @strSort = ''
' DECLARE @SortColumn ' + @type + ' --Select count(1) FROM ' + @Tables + @strFilter+' set @TotalCount=(Select count(1) FROM ' + @Tables + @strFilter+')'+' IF @ENDCOUNT > @TotalCount Select @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + @strSort + ' SET ROWCOUNT ' + @strPageSize + ' Select ' + @Fields + ' FROM ' + '(Select *,ROW_NUMBER() OVER ('+@STRORDER+')as RowNumber FROM '+@Tables+' where 1=1 '+@strSimpleFilter+') t' + ' Where t.RowNumber between '+@strStartRow+' and @strENDCOUNT ' + @strGroup + @strSort + ' ') |