--顯示簽合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