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

发布时间：2020-12-12 14:11:45 所属栏目：MsSql教程来源：网络整理

导读：CREATE PROC [dbo].[Mypage] @tableName SYSNAME,-- 表名 @keyField NVARCHAR(1000),-- 主键,多个主键有逗号分隔开 @pageIndex INT=1,-- 要显示的页码 @pageSize INT=10,-- 每页的大小(记录数) @fieldName NVARCHAR(1000)='',-- 要显示的字段列表 @sortName

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"不是表,视图或者表值函数',@tableName)

          RETURN
      END

    --分页字段检查
    IF Isnull(@keyField,N'') = ''
      BEGIN
          RAISERROR(N'分页处理需要主键(或者惟一键)',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,Charindex(N',@s),N''),@Where1 = @Where1 + N' AND a.'
                             + LEFT(@s,@s)-1) + N'='
                             + LEFT(@s,@s)-1)

          SELECT @Where1 = Stuff(@Where1 + N' AND a.' + @s + N'=' + @s,5,@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,')
            SET @new_order2 = ' ORDER BY ' + SUBSTRING(@new_order2,LEN(@new_order2)-1)            
            IF @FieldList <> '*'
                BEGIN            
                    SET @new_order3 = REPLACE(REPLACE(@Order + ',')                              
                    SET @FieldList = ',' + @FieldList                    
                    WHILE CHARINDEX(',@new_order3)>0
                    BEGIN
                        IF CHARINDEX(SUBSTRING(','+@new_order3,CHARINDEX(',@new_order3)),'+@FieldList+',')>0
                        BEGIN 
                        SET @FieldList = 
                            @FieldList + ',' + SUBSTRING(@new_order3,@new_order3))                        
                        END
                        SET @new_order3 = 
                        SUBSTRING(@new_order3,@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快速分页
CREATE 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,@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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!