分頁存儲過程的改良

--顯示簽合ApplyTab表中的相關查詢記錄 --創建人：朱保健 --創建時間：20080509 --exec  sp_Get_ApplyStatusOKBySearch '','0','20','','' ALTER                  proc sp_Get_ApplyStatusOKBySearch (  @where    nvarchar(3000), --where条件  @CurrentPage int,   --当前页数，从0开始  @PageSize int,--每页的数量  @recordCount  int out,   @pageCount  int out ) as declare @sqlStr nvarchar(3000) declare @FieldList nvarchar(3000) declare @TableName nvarchar(50) declare @KeyName nvarchar(50) declare @Filter nvarchar(3000) declare @OrderList nvarchar(3000) Declare @strGetRecordCount nvarchar(4000)--得到记录数的语句 set @OrderList = '  order by ApplyTime desc' if ( @where=''  or  @where is null) begin set @Filter= ' 1>0  and ApplyStatus=1 and NegativeStatus=0 ' end else begin set @Filter=@where+'  and ApplyStatus=1 and NegativeStatus=0  ' --set @Filter =  ' 1>0  ' +@where end set @KeyName='ApplyID' set @TableName = 'ApplyTab' set @FieldList='  ApplyID, StuffID, BatchID, case StuffClass when ''0'' then ''comp'' when ''1'' then ''sold'' when ''2'' then ''comp/sold'' when ''3'' then ''L2 '' when ''4'' then ''L3'' when ''5'' then ''L4'' when ''6'' then ''L5'' when ''7'' then ''L2/L3'' when ''8'' then ''L4/L5'' when ''9'' then ''sesc'' when ''10'' then ''sess'' when ''11'' then ''sesc/sess'' when ''12'' then ''sesc-a'' when ''13'' then ''sess-a'' when ''14'' then ''sesc-a/sess-a'' when ''15'' then ''pepc+10'' when ''16'' then ''peps+10'' when ''17'' then ''pepc+10/peps+10'' when ''18'' then ''spbc'' when ''19'' then ''spbs'' when ''20'' then ''spbc/spbs'' when ''21'' then ''seb-c-sau'' when ''22'' then ''seb-s-sau'' when ''23'' then ''seb-c-sau/seb-s-sau'' when ''24'' then ''mmc-c-sau-n'' when ''25'' then ''mmc-s-sau-n'' when ''26'' then ''mmc-c-sau-n/mmc-s-sau-n'' when ''27'' then ''mmc-c-hau-p'' when ''28'' then ''mmc-s-hau-p'' when ''29'' then ''mmc-c-hau-p/mmc-s-hau-p'' when ''30'' then ''apbc-p'' when ''31'' then ''apbS-p'' when ''32'' then ''apbc-p/apbS-p'' when ''33'' then ''drill'' when ''34'' then ''marking'' end as  StuffClass, NegativeStatus, AdjManager, ApplyManager, ApplyStatus, ApplyTime, FlowChart, ApplyUnit, Remark, SureTime' set @strGetRecordCount = 'select @RecordCount = count( distinct '+ @KeyName + ') from ' + @TableName + ' where ' + @Filter  exec sp_executesql  @strGetRecordCount ,    N'@RecordCount int out',   @RecordCount out

 if @PageSize = -1  begin   set @PageSize = @RecordCount  end    --取出總數  if @RecordCount !=0  begin   set @pageCount = ceiling( @RecordCount*1.0 / @PageSize )  end  else  begin   set @pageCount = 0  end

 if @pageCount != 0  begin   if  @CurrentPage >= @pageCount   begin    set @CurrentPage = @pageCount -1   end  end

 set @sqlStr = ' select top ' + Convert( nvarchar( 50 ) , @PageSize )+ @FieldList + ' from ' + @TableName  set @sqlStr = @sqlStr + ' where ' + @KeyName + ' not in ' + ' ( select top ' + Convert( nvarchar( 50 ) , @PageSize * @CurrentPage ) +'  '+ @KeyName + ' from ' + @TableName +' where  '+@Filter+' '+@OrderList+ ') and '+@Filter+ @OrderList exec(@sqlStr) print @sqlStr

 

 

 #region 獲取線別相關信息
    public DataSet Search_ProLineInfo(string where, int valCurrentPage, int pagesize, out int recordCount, out int pageCount)
    {
        ds = new DataSet();
        this.myCommand = new SqlCommand();

        myCommand.CommandText = "sp_Search_ProLineInfo";
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Connection = this.dataConn;

        myCommand.Parameters.Add(new SqlParameter("@where", SqlDbType.NVarChar));
        myCommand.Parameters.Add(new SqlParameter("@CurrentPage", SqlDbType.Int));  //
        myCommand.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int));   //
        myCommand.Parameters.Add(new SqlParameter("@recordCount", SqlDbType.Int));  //
        myCommand.Parameters.Add(new SqlParameter("@pageCount", SqlDbType.Int));  //

        myCommand.Parameters["@where"].Value = where;
        myCommand.Parameters["@CurrentPage"].Value = valCurrentPage;
        myCommand.Parameters["@PageSize"].Value = pagesize;

        myCommand.Parameters["@recordCount"].Direction = ParameterDirection.Output;
        myCommand.Parameters["@pageCount"].Direction = ParameterDirection.Output;

        this.sqlDataAdapter.SelectCommand = myCommand;

        sqlDataAdapter.Fill(ds);

        recordCount = Convert.ToInt32(myCommand.Parameters["@RecordCount"].Value);
        pageCount = Convert.ToInt32(myCommand.Parameters["@PageCount"].Value);

        return ds;
    }
    #endregion