I have a form that returns order details based on the input received in 4 parameters using the following WHERE clause
WHERE HOrder.Acno = @Acno and SUBSTRING(HDespatch.ArcOrdno,1,4) = @Unit and SUBSTRING(HDespatch.ArcOrdno,5,8) >= @FromDate and SUBSTRING(HDespatch.ArcOrdno,5,8) <= @ToDate
The current WHERE clause expects all these fields to contain data, but I want to expand the clause so that it will cater for the following.
If the Acno is blank then it will return all the orders for the unit within the date range. If the unit is blank it will return all orders for the Acno for the date range entered. If the date range is blank it will return all the Acno's orders for that unit
Hope this makes sense and any help would be much appreciated
just keep in mind that persisting with it will really make manipulations complex. everywhere you use the individual parts you've to apply SUBSTRING functions and get them which will also have an adverse effect on performance too.
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
WHERE clause
Posted - 03/07/2012 : 09:29:10
