lundi 3 août 2015

How to give change working of having function dynamicaly on executing an sql statement?

I'm having a Sql code like as follows

     Select a.ItemCode, a.ItemDesc               
 From fn_BOM_Material_Master('A', @AsOnDate, @RptDate, @BranchID, @CompID)a          
 Left Outer Join fn_INV_AsOnDate_Stock(@StockDate, @AsOnDate, @RptDate, @BranchID, @CompID, @Finyear)b          
 On a.ItemCode=b.ItemCode and b.WarehouseCode<>'WAP'         
 and a.BranchID=b.BranchID and a.CompID=b.COmpID          
 Where a.ItemNatureCode = 'F' and a.BranchID = @BranchID and a.CompID = @CompID           
 Group by a.ItemCode, a.ItemDesc          
 Having sum(b.CBQty)<=0 

Here the problem is that im passing an "@ShowZeroStock" value as as bit if the "@ShowZeroStock" value is '1' then Having should not be validated or (i.e: All values from the table should be returned including zero)

So How to change the query based on passed bit value "@ShowZeroStock"

I can Use "If else " condition at the top and remove having in else part, but for a lengthy query i can't do the same.

Aucun commentaire:

Enregistrer un commentaire