case用法

 

---select 里写case

select (case when fprice=0 then fauxprice else fprice end) fprice, * from icstockbillentry where  finterid=369383

 

 

----where 里写case

Select FName From t_AuxItem Where t_AuxItem.FItemID=v2.FAuxPropID) As FAuxPropName
From ICStockBill v1 Inner Join ICStockBillEntry v2 On v1.FInterID = v2.FInterID
Inner Join t_ICItem t1 On v2.FItemID=t1.FItemID
Inner Join t_Stock t2 On v2.FDCStockID=t2.FItemID
Inner Join t_MeasureUnit t3 On v2.FUnitID=t3.FMeasureUnitID
Left Join t_StockPlace t11 On v2.FDCSPID=t11.FSPID
Left Join t_Currency t503 On v1.FCurrencyID=t503.FCurrencyID
Left Join t_MeasureUnit t4 On t1.FSecUnitID=t4.FMeasureUnitID
  Where v1.FTranType=41
   AND t1.FNumber>= Case When @BegItemNumber='' then t1.FNumber else @BegItemNumber end
  AND t1.FNumber<= case when @EndItemNumber='' then t1.FNumber else @EndItemNumber end
  AND t2.FNumber>= case when @BegStockNumber='' then t2.FNumber else @BegStockNumber end
  AND t2.FNumber<= case when @EndStockNumber='' then t2.FNumber else @EndStockNumber end
  AND V2.FBatchNo>= case when @BegBatchNumber='' then   V2.FBatchNo else @BegBatchNumber end
  AND V2.FBatchNo<= case when @EndBatchNumber='' then V2.FBatchNo else @EndBatchNumber end
  AND v1.FDATE>= case when @BegDate ='' then v1.FDATE else  @BegDate end
  And v1.FDate<= case when @EndDate='' then   v1.FDate else  @EndDate end
And FCancelLation=0