日期:2014-05-18 浏览次数:20510 次
SqlParameter[] parms = new SqlParameter[5]; parms[0] = new SqlParameter("@PNRNumber", search.PnrNumber); parms[1] = new SqlParameter("@InvoiceNumber", search.InvoiceNumber); parms[2] = new SqlParameter("@StarDate",search.StarDate);// parms[3] = new SqlParameter("@EndDate",search.EndDate);//search.StarDate search.EndDate均为datetime类型 parms[4] = new SqlParameter("@PaymentStatus",search.Status); DataTable table = SqlHelper.ExecuteDataTable(CommandType.StoredProcedure, "proc_search", parms); return table;
alter proc proc_search @PNRNumber varchar(20), @InvoiceNumber varchar(50), @StarDate datetime, @EndDate datetime, @PaymentStatus varchar(50) as declare @str varchar(2000) set @str='select PaymentInfo.PNR,PaymentInfo.EpositDate,PaymentInfo.InvoiceNumber,PaymentInfo.PayType,PaymentInfo.PaymentStatus, PaymentInfo.AgentCode,PaymentInfo.SubAgentCode,PassengerInfo.FirstName,PassengerInfo.LastName from PaymentInfo,PassengerInfo where PaymentInfo.PassengerId=PassengerInfo.PassengerId ' if(@PNRNumber<>'') begin set @str=@str+' and PaymentInfo.PNR='+@PNRNumber end if(@InvoiceNumber<>'') begin set @str=@str+' and PassengerInfo.InvoiceNumber='+@InvoiceNumber end if(@StarDate<>'' and @EndDate='') begin set @str=@str+' and PassengerInfo.EpositDate>'+@StarDate end if(@StarDate='' and @EndDate<>'') begin set @str=@str+' and PassengerInfo.EpositDate<'+@EndDate end if(@StarDate<>'' and @EndDate<>'') begin set @str=@str+' and PassengerInfo.EpositDate between'+@StarDate+' and '+@EndDate end if(@PaymentStatus<>'') begin set @str=@str+@PaymentStatus end set @str=@str+' order by PaymentInfo.EpositDate desc ' exec(@str)