Append to WHERE clause on condition
-
Hi, Is it possible to append more conditions to a WHERE clause dependant on a condition? This is what i'm trying to do at the moment with no joy. E.G. ... WHERE (@extOrderID IS NULL OR Orders.ExtOrderID = @extOrderID) AND (@orderDateAfter IS NULL AND @orderDateBefore IS NULL OR Orders.TimeOrdered >= @orderDateAfter AND Orders.TimeOrdered <= @orderDateBefore) AND (@requiredDispatchDateAfter IS NULL AND @requiredDispatchDateBefore IS NULL OR (DATEADD(day, (SELECT SLA FROM Product WHERE Product.ID = OrderItem.ProductID), Orders.TimeOrdered)) >= @requiredDispatchDateAfter AND (DATEADD(day, (SELECT SLA FROM Product WHERE Product.ID = OrderItem.ProductID), Orders.TimeOrdered)) <= @requiredDispatchDateBefore) AND (@customerForename IS NULL OR Customer.Forename LIKE @customerForename) AND (@customerSurname IS NULL OR Customer.Surname LIKE @customerSurname) AND (@customerPostCode IS NULL OR Address.PostCode LIKE @customerPostCode) AND CASE WHEN @orderStatus IS NOT NULL THEN CASE WHEN @statusChecked = 'False' AND Status.StatusName != @orderStatus THEN 1 WHEN @statusChecked = 'True' AND Status.StatusName = @orderStatus THEN 1 ELSE 0 END END <> 0 AND CASE WHEN @isSearch = 1 THEN Orders.IsActive = 1 END It is the last CASE which is not working, any suggestions would be appreciated! Phil
-
Hi, Is it possible to append more conditions to a WHERE clause dependant on a condition? This is what i'm trying to do at the moment with no joy. E.G. ... WHERE (@extOrderID IS NULL OR Orders.ExtOrderID = @extOrderID) AND (@orderDateAfter IS NULL AND @orderDateBefore IS NULL OR Orders.TimeOrdered >= @orderDateAfter AND Orders.TimeOrdered <= @orderDateBefore) AND (@requiredDispatchDateAfter IS NULL AND @requiredDispatchDateBefore IS NULL OR (DATEADD(day, (SELECT SLA FROM Product WHERE Product.ID = OrderItem.ProductID), Orders.TimeOrdered)) >= @requiredDispatchDateAfter AND (DATEADD(day, (SELECT SLA FROM Product WHERE Product.ID = OrderItem.ProductID), Orders.TimeOrdered)) <= @requiredDispatchDateBefore) AND (@customerForename IS NULL OR Customer.Forename LIKE @customerForename) AND (@customerSurname IS NULL OR Customer.Surname LIKE @customerSurname) AND (@customerPostCode IS NULL OR Address.PostCode LIKE @customerPostCode) AND CASE WHEN @orderStatus IS NOT NULL THEN CASE WHEN @statusChecked = 'False' AND Status.StatusName != @orderStatus THEN 1 WHEN @statusChecked = 'True' AND Status.StatusName = @orderStatus THEN 1 ELSE 0 END END <> 0 AND CASE WHEN @isSearch = 1 THEN Orders.IsActive = 1 END It is the last CASE which is not working, any suggestions would be appreciated! Phil
Are you meaning that when @isSearch = 1 then Order.IsActive must equal 1 to return the row? If so, no you can't do that, but you could do this
AND (@isSearch = 0 or Orders.IsActive = 1)
Regards, Rob Philpott.
-
Are you meaning that when @isSearch = 1 then Order.IsActive must equal 1 to return the row? If so, no you can't do that, but you could do this
AND (@isSearch = 0 or Orders.IsActive = 1)
Regards, Rob Philpott.
Rob Philpott wrote:
Are you meaning that when @isSearch = 1 then Order.IsActive must equal 1 to return the row?
Yes that is what I meant, can you think of possibly another way then of how I could dynamically create my where clause based on the parameters passed. I have found away to do this but it involves using an IF to see if @isSearch = 0 and then having the same query but with Order.IsActive = 1 on the end of one of them. Obviously I'd prefer to not do this as I'm just repeating code.
-
Rob Philpott wrote:
Are you meaning that when @isSearch = 1 then Order.IsActive must equal 1 to return the row?
Yes that is what I meant, can you think of possibly another way then of how I could dynamically create my where clause based on the parameters passed. I have found away to do this but it involves using an IF to see if @isSearch = 0 and then having the same query but with Order.IsActive = 1 on the end of one of them. Obviously I'd prefer to not do this as I'm just repeating code.
The clause in my previous post will do it.
Regards, Rob Philpott.
-
Rob Philpott wrote:
Are you meaning that when @isSearch = 1 then Order.IsActive must equal 1 to return the row?
Yes that is what I meant, can you think of possibly another way then of how I could dynamically create my where clause based on the parameters passed. I have found away to do this but it involves using an IF to see if @isSearch = 0 and then having the same query but with Order.IsActive = 1 on the end of one of them. Obviously I'd prefer to not do this as I'm just repeating code.
The only reaslistic alternative is to use dynamic SQL (sql strings) but that is plain ugly. Also chaining multiple conditionals can nail a query to the wall. What I often do is use the "required" conditions to get a subset into a temp table and then start deleting records from the temp table or use the conditional filtering on the temp table
Never underestimate the power of human stupidity RAH