MS SqlServer海量数据分页存储过程收集

最新推荐文章于 2018-11-22 20:59:55 发布

chutisun0039

最新推荐文章于 2018-11-22 20:59:55 发布

阅读量45

点赞数

文章标签：数据库

原文链接：https://my.oschina.net/smartsmile/blog/814662

版权

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.PageHelper wp = new MSCL.PageHelper();
        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>页";
        /*
        MSCL.PageHelper p = new PageHelper();
        p.CurrentPageIndex = AspNetPager1.CurrentPageIndex;
        p.FieldsName = "*";
        p.KeyField = "d_id";
        p.SortName = "d_id asc";
        p.TableName = "testtable";
        p.EndCondition = "count(*)";
        p.PageSize = AspNetPager1.PageSize;


        DataTable dt = p.QueryPagination();
        AspNetPager1.RecordCount = p.RecordCount;
        Repeater1.DataSource = dt;
        Repeater1.DataBind();
        AspNetPager1.CustomInfoHTML = " 共<b>" + p.RecordCount + "</b>条记录/";
        AspNetPager1.CustomInfoHTML += "<b>" + p.PageCount + "</b>页";
        AspNetPager1.CustomInfoHTML += " 当前第<font color=\"red\"><b>" + p.CurrentPageIndex + "</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

--利用ROW_NUMBER快速分页  
ALTER PROCEDURE [dbo].[pr_Pagination]  
@TableName varchar(500), --要进行分页的表，也可以用联接，如dbo.employee或dbo.employee INNER JOIN dbo.jobs ON (dbo.employee.job_id=dbo.jobs.job_id)  
@Fields varchar(500), --表中的字段，可以使用*代替  
@OrderField varchar(500), --要排序的字段  
@SqlWhere varchar(max), --WHERE子句  
@PageSize int, --分页的大小  
@PageIndex int, --要显示的页的索引  
@TotalRecord int output,--记录总数  
@TotalPage int output, --页的总数  
@WholeContion varchar (1000) --存储过程中的条件  
as  
  
begin  
    --Begin Tran  
    Declare @sql nvarchar(4000);    
    Declare @Record int; --记录总数    
    --利用WHERE子句进行过滤  
    if (isnumeric(@WholeContion)=1 )  
    begin  
        set @sql = 'select @Record = count(*) from ' + @TableName + ' where 1=1 ' + @SqlWhere  
        EXEC sp_executesql @sql,N'@Record int OUTPUT',@Record OUTPUT  
        if (CAST(@WholeContion as int ) < @Record )  
        begin  
            set @Record = CAST(@WholeContion as int )  
        end   
    end  
    else  
    begin  
        set @sql = 'select @Record = ' + @WholeContion + ' from ' + @TableName + ' where 1=1 ' + @SqlWhere  
  
        --执行sql语句得到记录总数  
        EXEC sp_executesql @sql,N'@Record int OUTPUT',@Record OUTPUT  
    end   
      
    select @TotalPage=CEILING((@Record+0.0)/@PageSize)  
    select @TotalRecord=@Record  
     --select @TotalRecord  
    --select @TotalPage  
    --根据特定的排序字段为为行分配唯一ROW_NUMBER的顺序  
    set @sql = 'select * from (select ROW_NUMBER() over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where 1=1 ' + @SqlWhere  
    --确保当前页的索引在合理的范围之内  
    if @PageIndex<=0     
       Set @PageIndex = 1  
    --得到当前页在整个结果集中准确的ROW_NUMBER值  
    Declare @StartRecord int    
    Declare @EndRecord int    
    set @StartRecord = (@PageIndex-1)*@PageSize + 1    
    set @EndRecord = @StartRecord + @PageSize - 1  
    --输出当前页中的数据  
  
    set @sql = @sql + ') as t' + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +   Convert(varchar(50),@EndRecord)  
	print @sql  
    Exec(@sql)  
  
    --If @@Error <> 0  
       --Begin  
           --RollBack Tran            
       --End  
    --Else  
       --Begin  
           --Commit Tran            
       --End       
end

转载于:https://my.oschina.net/smartsmile/blog/814662

chutisun0039

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MS SqlServer海量数据分页存储过程收集

CREATE PROC [dbo].[Mypage] @tableName SYSNAME,-- 表名 @keyField NVARCHAR(1000),-- 主键,多个主键有逗号分隔开 ...
复制链接

扫一扫