MS SqlServer海量数据分页存储过程收集代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】 MS SqlServer海量数据分页存储过程收集
作者:Dezai.CN / 发布于2013/5/12/ 1146

	CREATE PROC [dbo].[Mypage] @tableName SYSNAME,-- 表名
	 @keyField NVARCHAR(1000),-- 主键,多个主键有逗号分隔开
	 @pageIndex INT=1,-- 要显示的页码
	 @pageSize INT=10,-- 每页的大小(记录数)
	 @fieldName NVARCHAR(1000)='',-- 要显示的字段列表
	 @sortName NVARCHAR(1000)='',-- 以逗号分隔的排序字段列表(如:id asc,Name asc)用于指定排序顺序
	 @strWhere NVARCHAR(1000)='',-- 查询条件
	 @recordCount INT=0 OUTPUT,-- 总记录数
	 @pageCount INT=0 OUTPUT -- 总页数
	AS
	 SET NOCOUNT ON

	 --检查对象是否有效
	 IF Object_id(@tableName) IS NULL
	 BEGIN
	 RAISERROR(N'对象"%s"不存在',1,16,@tableName)

	 RETURN
	 END

	 IF Objectproperty(Object_id(@tableName), N'IsTable') = 0
	 AND Objectproperty(Object_id(@tableName), N'IsView') = 0
	 AND Objectproperty(Object_id(@tableName), N'IsTableFunction') = 0
	 BEGIN
	 RAISERROR(N'"%s"不是表,视图或者表值函数',1,16,@tableName)

	 RETURN
	 END

	 --分页字段检查
	 IF Isnull(@keyField, N'') = ''
	 BEGIN
	 RAISERROR(N'分页处理需要主键(或者惟一键)',1,16)

	 RETURN
	 END

	 --?其它参数检查及规范
	 IF Isnull(@pageIndex, 0) < 1
	 SET @pageIndex=1

	 IF Isnull(@pageSize, 0) < 1
	 SET @pageSize=10

	 IF Isnull(@fieldName, N'') = N''
	 SET @fieldName=N'*'

	 IF Isnull(@sortName, N'') = N''
	 SET @sortName=N''
	 ELSE
	 SET @sortName=N'ORDER BY ' + Ltrim(@sortName)

	 IF Isnull(@strWhere, N'') = N''
	 SET @strWhere=N''
	 ELSE
	 SET @strWhere=N'WHERE (' + @strWhere + N')'

	 --如果为NULL值,则计算总页数(可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算,对于不想计算总页数,可以给@pageCount赋值)
	 IF @pageCount IS NULL
	 BEGIN
	 DECLARE @sql NVARCHAR(4000)

	 SET @sql=N'SELECT @recordCount=COUNT(*)' + N' FROM '
	 + @tableName + N' ' + @strWhere

	 EXEC Sp_executesql
	 @sql,
	 N'@recordCount int OUTPUT',
	 @recordCount OUTPUT

	 SET @pageCount=Ceiling(@recordCount * 1.0 / @pageSize)
	 END

	 --计算分页显示 top N 值
	 DECLARE @TopN VARCHAR(20),
	 @TopN1 VARCHAR(20)

	 SELECT @TopN = @pageSize,
	 @TopN1 = @pageIndex * @pageSize

	 --第一页直接显示
	 IF @pageIndex = 1
	 EXEC(N'SELECT TOP '+@TopN +N' '+@fieldName +N' FROM '+@tableName +N' '+@strWhere +N' '+@sortName)
	 ELSE
	 BEGIN
	 --生成主键(唯一键)处理条件
	 DECLARE @Where1 NVARCHAR(4000),
	 @s NVARCHAR(1000)

	 SELECT @Where1 = N'',
	 @s = @keyField

	 WHILE Charindex(N',', @s) > 0
	 SELECT @s = Stuff(@s, 1, Charindex(N',', @s), N''),
	 @Where1 = @Where1 + N' AND a.'
	 + LEFT(@s, Charindex(N',', @s)-1) + N'='
	 + LEFT(@s, Charindex(N',', @s)-1)

	 SELECT @Where1 = Stuff(@Where1 + N' AND a.' + @s + N'=' + @s, 1, 5, N''),
	 @TopN = @TopN1 - @pageSize

	 EXEC(N'SET ROWCOUNT '+@TopN1 +N' SELECT '+@keyField +N' INTO # FROM '+@tableName +N' '+@strWhere +N' '+@sortName +N' SET ROWCOUNT '+@TopN +N' DELETE FROM #' +N' SELECT '+@fieldName +N' FROM '+@tableName +N' a WHERE EXISTS(SELECT * FROM # WHERE '+@Where1 +N') '+@sortName)
	 END

	

	

	

	

	

	protected void Page_Load(object sender, EventArgs e)
	 {
	 if (!IsPostBack)
	 {
	 BindGridViewData();
	 }
	 }

	 protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
	 {
	 #region 如果是绑定数据行,鼠标滑过变色
	 if (e.Row.RowType == DataControlRowType.DataRow)
	 {
	 //鼠标经过时，行背景色变
	 e.Row.Attributes["onmouseover"] = "ItemOver(this)";
	 //鼠标经过行变成手形
	 //e.Row.Style.Add("cursor", "hand");

	 }
	 #endregion

	 #region 鼠标单击行，打开窗口同时传出参数ID

	 //string ID = String.Empty;
	 //for (int i = 0; i < grvTestTable.Rows.Count; i++)
	 //{
	 // ID = grvTestTable.DataKeys[i].Value.ToString();

	 // grvTestTable.Rows[i].Attributes.Add("onclick", "window.open('Detail.aspx?PKID="+ID+" ')");

	 //}
	 #endregion
	 }

	 private void BindGridViewData()
	 {
	 string strCondition = "";
	 MSCL.Pageing wp = new MSCL.Pageing();
	 wp.TableName = "AFM_EmailMessage";
	 wp.KeyField = "EID";
	 wp.SortName = "EID";
	 wp.Condition = strCondition;
	 wp.CurrentPageIndex = AspNetPager1.CurrentPageIndex;
	 wp.PageSize = AspNetPager1.PageSize;//=PageSize;
	 DataTable dt = wp.GetDataTableMyPage();
	 AspNetPager1.RecordCount = wp.RecordCount;
	 grvAFM_EmailMessage.DataSource = dt;
	 grvAFM_EmailMessage.DataBind();
	 AspNetPager1.CustomInfoHTML = " 共<font color='#FF8000'><b>" + wp.RecordCount.ToString() + "</b></font>条记录/";
	 AspNetPager1.CustomInfoHTML += " <font color=#FF8000'><b>" + wp.PageCount.ToString() + "</b></font>页";
	 AspNetPager1.CustomInfoHTML += " 当前第<font color=\"red\"><b>" + AspNetPager1.CurrentPageIndex.ToString() + "</b></font>页";

	 }

	 protected void AspNetPager1_PageChanged(object sender, EventArgs e)
	 {
	 BindGridViewData();
	 }

	

	

	

	

	CREATE PROC P_viewPage
	 /*
	 敬告：适用于单一主键或存在唯一值列的表或视图
	
	 */
	 @TableName VARCHAR(200), --表名
	 @FieldList VARCHAR(2000), --显示列名，如果是全部字段则为*
	 @PrimaryKey VARCHAR(100), --单一主键或唯一值键
	 @Where VARCHAR(2000), --查询条件 不含'where'字符，如id>10 and len(userid)>9
	 @Order VARCHAR(1000), --排序 不含'order by'字符，如id asc,userid desc，必须指定asc或desc
	 --注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷
	 @SortType INT, --排序规则 1:正序asc 2:倒序desc 3:多列排序方法
	 @RecorderCount INT, --记录总数 0:会返回总记录
	 @PageSize INT, --每页输出的记录数
	 @PageIndex INT, --当前页数
	 @TotalCount INT OUTPUT, --记返回总记录
	 @TotalPageCount INT OUTPUT --返回总页数
	AS
	 SET NOCOUNT ON

	 IF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0
	 SET @Order = RTRIM(LTRIM(@Order))
	 SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))
	 SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','')

	 WHILE CHARINDEX(', ',@Order) > 0 OR CHARINDEX(' ,',@Order) > 0
	 BEGIN
	 SET @Order = REPLACE(@Order,', ',',')
	 SET @Order = REPLACE(@Order,' ,',',')
	 END

	 IF ISNULL(@TableName,'') = '' OR ISNULL(@FieldList,'') = ''
	 OR ISNULL(@PrimaryKey,'') = ''
	 OR @SortType < 1 OR @SortType >3
	 OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0
	 BEGIN
	 PRINT('ERR_00')
	 RETURN
	 END

	 IF @SortType = 3
	 BEGIN
	 IF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC')
	 BEGIN PRINT('ERR_02') RETURN END
	 END

	 DECLARE @new_where1 VARCHAR(1000)
	 DECLARE @new_where2 VARCHAR(1000)
	 DECLARE @new_order1 VARCHAR(1000)
	 DECLARE @new_order2 VARCHAR(1000)
	 DECLARE @new_order3 VARCHAR(1000)
	 DECLARE @Sql VARCHAR(8000)
	 DECLARE @SqlCount NVARCHAR(4000)

	 IF ISNULL(@where,'') = ''
	 BEGIN
	 SET @new_where1 = ' '
	 SET @new_where2 = ' WHERE '
	 END
	 ELSE
	 BEGIN
	 SET @new_where1 = ' WHERE ' + @where
	 SET @new_where2 = ' WHERE ' + @where + ' AND '
	 END

	 IF ISNULL(@order,'') = '' OR @SortType = 1 OR @SortType = 2
	 BEGIN
	 IF @SortType = 1
	 BEGIN
	 SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' ASC'
	 SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' DESC'
	 END
	 IF @SortType = 2
	 BEGIN
	 SET @new_order1 = ' ORDER BY ' + @PrimaryKey + ' DESC'
	 SET @new_order2 = ' ORDER BY ' + @PrimaryKey + ' ASC'
	 END
	 END
	 ELSE
	 BEGIN
	 SET @new_order1 = ' ORDER BY ' + @Order
	 END

	 IF @SortType = 3 AND CHARINDEX(','+@PrimaryKey+' ',','+@Order)>0
	 BEGIN
	 SET @new_order1 = ' ORDER BY ' + @Order
	 SET @new_order2 = @Order + ','
	 SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},')
	 SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,')
	 SET @new_order2 = ' ORDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)
	 IF @FieldList <> '*'
	 BEGIN
	 SET @new_order3 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',')
	 SET @FieldList = ',' + @FieldList
	 WHILE CHARINDEX(',',@new_order3)>0
	 BEGIN
	 IF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0
	 BEGIN
	 SET @FieldList =
	 @FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3))
	 END
	 SET @new_order3 =
	 SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3))
	 END
	 SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList))
	 END
	 END

	 SET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'
	 + CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName + @new_where1
	
	 IF @RecorderCount = 0
	 BEGIN
	 EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',
	 @TotalCount OUTPUT,@TotalPageCount OUTPUT
	 END
	 ELSE
	 BEGIN
	 SELECT @TotalCount = @RecorderCount
	 END

	 IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)
	 BEGIN
	 SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)
	 END

	 IF @PageIndex = 1 OR @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)
	 BEGIN
	 IF @PageIndex = 1 --返回第一页数据
	 BEGIN
	 SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
	 + @TableName + @new_where1 + @new_order1
	 END
	 IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最后一页数据
	 BEGIN
	 SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
	 + 'SELECT TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))
	 + ' ' + @FieldList + ' FROM '
	 + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
	 + @new_order1
	 END
	 END
	 ELSE
	 BEGIN
	 IF @SortType = 1 --仅主键正序排序
	 BEGIN
	 IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
	 BEGIN
	 SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
	 + @TableName + @new_where2 + @PrimaryKey + ' > '
	 + '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP '
	 + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey
	 + ' FROM ' + @TableName
	 + @new_where1 + @new_order1 +' ) AS TMP) '+ @new_order1
	 END
	 ELSE --反向检索
	 BEGIN
	 SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
	 + 'SELECT TOP ' + STR(@PageSize) + ' '
	 + @FieldList + ' FROM '
	 + @TableName + @new_where2 + @PrimaryKey + ' < '
	 + '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP '
	 + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey
	 + ' FROM ' + @TableName
	 + @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2
	 + ' ) AS TMP ' + @new_order1
	 END
	 END
	 IF @SortType = 2 --仅主键反序排序
	 BEGIN
	 IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
	 BEGIN
	 SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
	 + @TableName + @new_where2 + @PrimaryKey + ' < '
	 + '(SELECT MIN(' + @PrimaryKey + ') FROM (SELECT TOP '
	 + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey
	 +' FROM '+ @TableName
	 + @new_where1 + @new_order1 + ') AS TMP) '+ @new_order1
	 END
	 ELSE --反向检索
	 BEGIN
	 SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
	 + 'SELECT TOP ' + STR(@PageSize) + ' '
	 + @FieldList + ' FROM '
	 + @TableName + @new_where2 + @PrimaryKey + ' > '
	 + '(SELECT MAX(' + @PrimaryKey + ') FROM (SELECT TOP '
	 + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey
	 + ' FROM ' + @TableName
	 + @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2
	 + ' ) AS TMP ' + @new_order1
	 END
	 END
	 IF @SortType = 3 --多列排序，必须包含主键，且放置最后，否则不处理
	 BEGIN
	 IF CHARINDEX(',' + @PrimaryKey + ' ',',' + @Order) = 0
	 BEGIN PRINT('ERR_02') RETURN END
	 IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
	 BEGIN
	 SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
	 + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
	 + ' SELECT TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldList
	 + ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP '
	 + @new_order2 + ' ) AS TMP ' + @new_order1
	 END
	 ELSE --反向检索
	 BEGIN
	 SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
	 + 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
	 + ' SELECT TOP ' + STR(@TotalCount-@PageSize*@PageIndex+@PageSize) + ' ' + @FieldList
	 + ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
	 + @new_order1 + ' ) AS TMP ' + @new_order1
	 END
	 END
	 END
	 PRINT(@Sql)
	 EXEC(@Sql)
	GO
试试其它关键字
　分页存储过程　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)