一个很好用的存储过程分页

在网上找过几个存储过程分页，觉得NickLee的最好用，原帖点这里，这里转一下，也修改了一些错误

 

/*****************************************************************

* 存储过程名 : GetCustomersDataPage

* 过程描述 : 通用大数据集分页

* 传入参数：

* 传出参数：

* 修改记录

* 姓名   日期   修改类型

* NickLee       2005 －－     新建

*

*

*

*

*

******************************************************************/

-- 获取指定页的数据

 

CREATE  PROCEDURE [GetCustomersDataPage]

         @PageIndex INT,                             -- 页面索引 , 从 datagrid 中获取

         @PageSize INT,                              -- 页面显示数量，从 datagrid 中获取

         @RecordCount INT OUT,                -- 返回记录总数

         @PageCount INT OUT,                   -- 返回分页后页数

         @strGetFields nvarchar(1000),         -- 需要查询的列

         @tableName nvarchar(500) ,           -- 表名称

         @ID nvarchar(100),                          -- 主键 , （为表的主键）

         @strWhere nvarchar(1000) ='',        -- 查询条件 ( 注意 : 不要加 where)

         @sortName nvarchar(50) =' asc ' ,     -- 排序方式

         @orderName nvarchar(100)              -- 父级查询排序方式

 

AS

declare @countSelect nvarchar(2000) 

-- 设置统计查询语句

if len(@strWhere) =0

-- 如果没有查询条件

    begin

        set @countSelect=N'SELECT @CountRecord = COUNT(*) FROM '+@tableName

    end

else

-- 否则

    begin

        set @countSelect=N'SELECT @CountRecord = COUNT(*) FROM '+@tableName+' where '+@strWhere

    end

-- 执行并返回总数

exec sp_executesql @countSelect,N'@CountRecord int output',@RecordCount output

SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)

 

SET NOCOUNT ON

 

DECLARE @SQLSTR NVARCHAR(3000)

-- 实际总共的页码小于当前页码或者最大页码

if @PageCount>=0

    -- 如果分页后页数大于

    begin

        if @PageCount<=@PageIndex and @PageCount>0   -- 如果实际总共的页数小于 datagrid 索引的页数

            --or @PageCount=1

            begin

                -- 设置为最后一页

    set @PageIndex=@PageCount-1

            end

        else if @PageCount<=@PageIndex and @PageCount=0

            begin

                set @PageIndex=0;

            end

    end

 

IF @PageIndex = 0 OR @PageCount <= 1 -- 如果为第一页

    begin

        if len(@strWhere) =0

            begin

                SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+@strGetFields+' FROM '+@tableName+' ORDER BY '+@orderName+@sortName

            end

        else

            begin

                SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+@strGetFields+' FROM '+@tableName+' where '+@strWhere+' ORDER BY '+@orderName+@sortName

            end

    end

ELSE IF     @PageIndex = @PageCount - 1 -- 如果为最后一页           

    begin

        if len(@strWhere) =0

            begin

                SET @SQLSTR =N' SELECT '+@strGetFields+' FROM '+@tableName+' where '+@ID+' not in ( SELECT TOP '+STR(/*@RecordCount - */@PageSize * @PageIndex )+@ID+' FROM '+@tableName+' ORDER BY '+@orderName+@sortName+' )   ORDER BY '+@orderName+@sortName

            end

        else

            begin

                SET @SQLSTR =N' SELECT '+@strGetFields+' FROM '+@tableName+' where '+@ID+' not in ( SELECT TOP '+STR(/*@RecordCount - */ @PageSize * @PageIndex )+@ID+' FROM '+@tableName+' where '+@strWhere+' ORDER BY '+@orderName+@sortName+' ) and '+@strWhere+' ORDER BY '+@orderName+@sortName

            end

    end

ELSE                                                               -- 否则执行

    begin

         if len(@strWhere) =0

            begin

                SET @SQLSTR =N' SELECT TOP '+STR( @PageSize )+@strGetFields+' FROM '+@tableName+' where '+@ID+' not in ( SELECT TOP '+STR( /*@RecordCount - */@PageSize * @PageIndex )+@ID+' FROM '+@tableName+' ORDER BY '+@orderName+@sortName+' ) ORDER BY '+@orderName+@sortName

            end

         else

            begin

                SET @SQLSTR =N' SELECT TOP '+STR( @PageSize )+@strGetFields+' FROM '+@tableName+' where '+@ID+' not in (SELECT TOP '+STR(/*@RecordCount - */ @PageSize * @PageIndex )+@ID+' FROM '+@tableName+' where '+@strWhere+' ORDER BY '+@orderName+@sortName+' )and '+@strWhere+'ORDER BY '+@orderName+@sortName

            end

    end

EXEC (@SQLSTR)

set nocount off

GO

 

 

在asp.net中调用方法：

#region 声明

//----------------------------------------------------------------------

//

//

// 作者: 李淼（Nick.Lee）

//

// 存储过程DataGrid分页及注意点

//

//

// boyorgril@msn.com

//

//----------------------------------------------------------------------

#endregion

 

 private void DataGridDataBind()

  {

       DataSet ds = GetCustomersData(PageIndex,PageSize,ref recordCount,ref pageCount);

 

       DataGrid1.VirtualItemCount = RecordCount;

       DataGrid1.DataSource = ds;

       DataGrid1.DataBind();

    //   GridExpand(this.DataGrid1,2);

       SetPagingState();

  }

 

  private  DataSet GetCustomersData(int pageIndex,int pageSize,ref int recordCount,ref int pageCount)

  {

       dataFill.ConString=System.Configuration.ConfigurationSettings.AppSettings["SqlConnectionString"];

       dataFill.sqlClientDataSet("GetCustomersDataPage");

       System.Data.SqlClient.SqlDataAdapter comm=dataFill.mySqlAdapter;

 

       comm.SelectCommand.Parameters.Add(new SqlParameter("@PageIndex",SqlDbType.Int));

       comm.SelectCommand.Parameters[0].Value = pageIndex;

       comm.SelectCommand.Parameters.Add(new SqlParameter("@PageSize",SqlDbType.Int));

       comm.SelectCommand.Parameters[1].Value = pageSize;

       comm.SelectCommand.Parameters.Add(new SqlParameter("@RecordCount",SqlDbType.Int));

       comm.SelectCommand.Parameters[2].Direction = ParameterDirection.Output;

       comm.SelectCommand.Parameters.Add(new SqlParameter("@PageCount",SqlDbType.Int));

       comm.SelectCommand.Parameters[3].Direction = ParameterDirection.Output;

 

       comm.SelectCommand.Parameters.Add(new SqlParameter("@strGetFields",SqlDbType.NVarChar));

       comm.SelectCommand.Parameters[4].Value ="tOrder.orderTime as '下订单时间',tOrder.facName as '工厂',tOrder.facOrderNum as '工厂订单号',tOrder.quantity as '定单数',tOrder.realQuantity as '实际出货数',tOrder.reqTime as '要求出货时间',tOrder.repTime as '出货时间',tMaterial.matName as '材料',tMaterial.colName as '颜色',tOrder.leaveQuantity as '未出货数',tOrder.orderStatic as '全部出货',tOrder.orderDetail as '备注' ";

       /*tOrder.comName as '公司',tOrder.comOrderNum as '公司订单号',*/

       comm.SelectCommand.Parameters.Add(new SqlParameter("@tableName",SqlDbType.NVarChar));

       comm.SelectCommand.Parameters[5].Value =" tOrder left join tStock on tOrder.stoID=tStock.stoID left join tMaterial on tStock.matID=tMaterial.matID ";

       comm.SelectCommand.Parameters.Add(new SqlParameter("@ID",SqlDbType.NVarChar));

       comm.SelectCommand.Parameters[6].Value =" tOrder.orderID ";

       comm.SelectCommand.Parameters.Add(new SqlParameter("@orderName",SqlDbType.NVarChar));

       comm.SelectCommand.Parameters[7].Value =" tMaterial.matName ";

       comm.SelectCommand.Parameters.Add(new SqlParameter("@strWhere",SqlDbType.NVarChar));

       comm.SelectCommand.Parameters[8].Value =" facName='"+en1.decyrpt(this.Request.QueryString["facName"].ToString())+"' and facOrderNum='"+en1.decyrpt(this.Request.QueryString["facNum"].ToString())+"' ";

       //   comm.Parameters.Add(new SqlParameter("@sortName",SqlDbType.NVarChar));

       //   comm.Parameters[8].Value =" desc ";

 

       comm.Fill(dataFill.myDateSet);

 

       recordCount = (int)comm.SelectCommand.Parameters[2].Value;

       pageCount = (int)comm.SelectCommand.Parameters[3].Value;

 

       if(pageIndex>=pageCount&&pageCount>0)

       {

        PageIndex=pageCount-1;

       }

       else if(pageIndex>=pageCount&&pageCount==0)

       {

        PageIndex=0;

       }

       //  

       return dataFill.myDateSet;

 

  }

 

  /// <summary>

  /// 控制导航按钮或数字的状态

  /// </summary>

  public void SetPagingState()

  {

       if( PageCount <= 1 )//( RecordCount <= PageSize )//小于等于一页

       {

        this.Menu1.Items[0].Enabled = false;

        this.Menu1.Items[1].Enabled = false;

        this.Menu1.Items[2].Enabled = false;

        this.Menu1.Items[3].Enabled = false;

       }

       else //有多页

       {

        if( PageIndex == 0 )//当前为第一页

        {

         this.Menu1.Items[0].Enabled = false;

         this.Menu1.Items[1].Enabled = false;

         this.Menu1.Items[2].Enabled = true;

         this.Menu1.Items[3].Enabled = true;                                  

        }

 

        else if( PageIndex == PageCount - 1 )//当前为最后页

        {

         this.Menu1.Items[0].Enabled = true;

         this.Menu1.Items[1].Enabled = true;

         this.Menu1.Items[2].Enabled = false;

         this.Menu1.Items[3].Enabled = false;                                  

        }

        else //中间页

        {

         this.Menu1.Items[0].Enabled = true;

         this.Menu1.Items[1].Enabled = true;

         this.Menu1.Items[2].Enabled = true;

         this.Menu1.Items[3].Enabled = true;

        }

       }

       if(RecordCount == 0)

       {

        lab_PageCount.Text="第页共页每页"+PageSize.ToString()+"条共"+RecordCount.ToString()+"条";

       }   

       else

       {

        lab_PageCount.Text="第"+(PageIndex + 1).ToString()+"页共"+PageCount.ToString()+"页每页"+PageSize.ToString()+"条共"+RecordCount.ToString()+"条";

       }

  }

 

  #endregion

 

  //重点在数据对datagrid绑定前进行判定

  if(pageIndex>=pageCount&&pageCount>0)

   {

        PageIndex=pageCount-1;

   }

   else if(pageIndex>=pageCount&&pageCount==0)

   {

        PageIndex=0;

   }