SQL: IF clause within WHERE clause

Is it possible to use an **IF** clause within a **WHERE** clause in MS SQL?

Example:

WHERE
IF IsNumeric(@OrderNumber) = 1
OrderNumber = @OrderNumber
ELSE
OrderNumber LIKE '%' + @OrderNumber + '%'

Use a [CASE][1] statement instead of IF.


[1]:

[To see links please register here]

You should be able to do this without any IF or CASE

WHERE
(IsNumeric(@OrderNumber) AND
(CAST OrderNumber AS VARCHAR) = (CAST @OrderNumber AS VARCHAR)
OR
(NOT IsNumeric(@OrderNumber) AND
OrderNumber LIKE ('%' + @OrderNumber))

Depending on the flavour of SQL you may need to tweak the casts on the order number to an INT or VARCHAR depending on whether implicit casts are supported.

This is a very common technique in a WHERE clause. If you want to apply some "IF" logic in the WHERE clause all you need to do is add the extra condition with an boolean AND to the section where it needs to be applied.

There isn't a good way to do this in SQL. Some approaches I have seen:

1) Use CASE combined with boolean operators:

WHERE
OrderNumber = CASE
WHEN (IsNumeric(@OrderNumber) = 1)
THEN CONVERT(INT, @OrderNumber)
ELSE -9999 -- Some numeric value that just cannot exist in the column
END
OR
FirstName LIKE CASE
WHEN (IsNumeric(@OrderNumber) = 0)
THEN '%' + @OrderNumber
ELSE ''
END

2) Use IF's outside the SELECT

IF (IsNumeric(@OrderNumber)) = 1
BEGIN
SELECT * FROM Table
WHERE @OrderNumber = OrderNumber
END ELSE BEGIN
SELECT * FROM Table
WHERE OrderNumber LIKE '%' + @OrderNumber
END

3) Using a long string, compose your SQL statement conditionally, and then use EXEC

The 3rd approach is hideous, but it's almost the only think that works if you have a number of variable conditions like that.

Use a [CASE][1] statement
**UPDATE:** The previous syntax (as pointed out by a few people) doesn't work. You can use CASE as follows:

WHERE OrderNumber LIKE
CASE WHEN IsNumeric(@OrderNumber) = 1 THEN
@OrderNumber
ELSE
'%' + @OrderNumber
END

Or you can use an IF statement like @[N. J. Reed][2] points out.


[1]:

[To see links please register here]

[2]:

[To see links please register here]

You want the CASE statement

WHERE OrderNumber LIKE
CASE WHEN IsNumeric(@OrderNumber)=1 THEN @OrderNumber ELSE '%' + @OrderNumber END

I think that where...like/=...case...then... can work with Booleans. I am using T-SQL.

Scenario: Let's say you want to get Person-30's hobbies if bool is false, and Person-42's hobbies if bool is true. (According to some, hobby-lookups comprise over 90% of business computation cycles, so pay close attn.).

CREATE PROCEDURE sp_Case
@bool bit
AS
SELECT Person.Hobbies
FROM Person
WHERE Person.ID =
case @bool
when 0
then 30
when 1
then 42
end;

<pre>
WHERE (IsNumeric(@OrderNumber) <> 1 OR OrderNumber = @OrderNumber)
AND (IsNumber(@OrderNumber) = 1 OR OrderNumber LIKE '%'
+ @OrderNumber + '%')
</pre>

If @LstTransDt is Null
begin
Set @OpenQty=0
end
else
begin
Select @OpenQty=IsNull(Sum(ClosingQty),0)
From ProductAndDepotWiseMonitoring
Where Pcd=@PCd And PtpCd=@PTpCd And TransDt=@LstTransDt
end


See if this helps.

USE AdventureWorks2012;
GO
IF
(SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5
PRINT 'There are more than 5 Touring-3000 bicycles.'
ELSE PRINT 'There are 5 or less Touring-3000 bicycles.' ;
GO