query - sql if statement in select
SQL: IF clause within WHERE clause (8)
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;
Is it possible to use an IF clause within a WHERE clause in MS SQL?
WHERE IF IsNumeric(@OrderNumber) = 1 OrderNumber = @OrderNumber ELSE OrderNumber LIKE '%' + @OrderNumber + '%'
The following example executes a query as part of the Boolean expression and then executes slightly different statement blocks based on the result of the Boolean expression. Each statement block starts with BEGIN and completes with END.
USE AdventureWorks2012; GO DECLARE @AvgWeight decimal(8,2), @BikeCount int IF (SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5 BEGIN SET @BikeCount = (SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%'); SET @AvgWeight = (SELECT AVG(Weight) FROM Production.Product WHERE Name LIKE 'Touring-3000%'); PRINT 'There are ' + CAST(@BikeCount AS varchar(3)) + ' Touring-3000 bikes.' PRINT 'The average weight of the top 5 Touring-3000 bikes is ' + CAST(@AvgWeight AS varchar(8)) + '.'; END ELSE BEGIN SET @AvgWeight = (SELECT AVG(Weight) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ); PRINT 'Average weight of the Touring-3000 bikes is ' + CAST(@AvgWeight AS varchar(8)) + '.' ; END ; GO
Using nested IF...ELSE statements The following example shows how an IF … ELSE statement can be nested inside another. Set the @Number variable to 5, 50, and 500 to test each statement.
DECLARE @Number int SET @Number = 50 IF @Number > 100 PRINT 'The number is large.' ELSE BEGIN IF @Number < 10 PRINT 'The number is small' ELSE PRINT 'The number is medium' END ; GO
Use a CASE statement instead of IF.
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.
You want the CASE statement
WHERE OrderNumber LIKE CASE WHEN IsNumeric(@OrderNumber)=1 THEN @OrderNumber ELSE '%' + @OrderNumber END
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