Doing an IF in SQL WHERE

Question

I want to do this

SELECT *
  FROM sales_order_header       
 WHERE order_reference LIKE @order_reference + '%'
   AND ((@booked_in IS NULL) OR ( booked_in = @booked_in))
   AND ((@depot_assigned IS NULL) OR ( IF @depot_assigned = 'Y' depot_code <> ' ' ELSE depot_code = ' ') )

I'm sure you can all guess the OR ( IF @depot_assigned etc ) bit is failing.

How can i do something like this or do i have to do it as two separate select statements depending on the value of the @depot_assigned parameter.

Answer 1

The T-SQL CASE statement is what you're looking for. The modification to your code below should work for you:

SELECT
    *
FROM
    sales_order_header

WHERE
    order_reference LIKE @order_reference + '%'         AND
    ((@booked_in IS NULL) OR ( booked_in = @booked_in)) AND
    ((@depot_assigned IS NULL) OR (CASE WHEN @depot_assigned = 'Y' THEN depot_code <> ' ' ELSE depot_code = ' ' END) )

Answer 2

SELECT
        *

    FROM
        sales_order_header

    WHERE
        order_reference LIKE @order_reference + '%'         AND
        ((@booked_in IS NULL) OR ( booked_in = @booked_in)) AND
        ((@depot_assigned IS NULL) OR 
            ( @depot_assigned = 'Y' AND depot_code <> ' ' ) OR
            ( @depot_assigned <> 'Y' AND depot_code = ' ') )

Answer 3

When you build a query like the one you attempt the query optimizer will have to create one plan to fit any value of the parameters. This will result in the worst possible plan. So even if you come up with the right expression (using CASE), the query will be very badly performing due to the contradicting OR conditions.

Separate the IFs into SQL conditions and use distinct queries for each case:

IF @booked_in IS NULL and @depot_assigned IS NULL
  SELECT * FROM sales_order_header
  WHERE order_reference LIKE @order_reference + '%'
ELSE IF @depot_assigned IS NULL
  SELECT * FROM sales_order_header
  WHERE order_reference LIKE @order_reference + '%'
  AND booked_in = @booked_in
ELSE IF @booked_in IS NULL AND @depot_code='Y'
  SELECT * FROM sales_order_header
  WHERE order_reference LIKE @order_reference + '%'
  AND depot_code <> ' '
ELSE IF IF @booked_in IS NULL AND @depot_code<>'Y'
  SELECT * FROM sales_order_header
  WHERE order_reference LIKE @order_reference + '%'
  AND depot_code = ' '
ELSE IF @depot_code = 'Y'
  SELECT * FROM sales_order_header
  WHERE order_reference LIKE @order_reference + '%'
  AND booked_in = @booked_in
  AND depot_code <> ' '
ELSE
  SELECT * FROM sales_order_header
  WHERE order_reference LIKE @order_reference + '%'
  AND booked_in = @booked_in
  AND depot_code = ' '

Despite the apparent ungliness and lack of elegance, this is the best approach. Its main draw back is maintenability problems, and to cure that there is a viable alternative in the use dynamic SQL to build the query.

Ultimately, the complexity of the IF conditions are a code smell that your data access API does too many things that should be separate API entry points.

Answer 4

SELECT * FROM sales_order_header       
WHERE 
order_reference LIKE @order_reference + '%'
AND 
booked_in = ISNULL(@booked_in,booked_in)
AND 
CASE 
    WHEN @depot_assigned IS NULL THEN 1 
    WHEN @depot_assigned  = 'Y' AND depot_code <> ' ' THEN 1 
    WHEN @depot_assigned <> 'Y' AND depot_code  = ' ' THEN 1
    ELSE 0 
END = 1

EDIT: Similar to some of the other answers. Just slightly more simplified.