How do I perform an IF...THEN in an SQL SELECT?
How do I perform an
IF...THEN in an
SQL SELECT statement?
SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product
CASE statement is the closest to IF in SQL and is supported on all versions of SQL Server.
SELECT CAST( CASE WHEN Obsolete = 'N' or InStock = 'Y' THEN 1 ELSE 0 END AS bit) as Saleable, * FROM Product
You only need to do the
CAST if you want the result as a Boolean value. If you are happy with an
int, this works:
SELECT CASE WHEN Obsolete = 'N' or InStock = 'Y' THEN 1 ELSE 0 END as Saleable, * FROM Product
CASE statements can be embedded in other
CASE statements and even included in aggregates.
SELECT IIF(Obsolete = 'N' or InStock = 'Y', 1, 0) as Saleable, * FROM Product
Read more... Read less...
The case statement is your friend in this situation, and takes one of two forms:
The simple case:
SELECT CASE <variable> WHEN <value> THEN <returnvalue> WHEN <othervalue> THEN <returnthis> ELSE <returndefaultcase> END AS <newcolumnname> FROM <table>
The extended case:
SELECT CASE WHEN <test> THEN <returnvalue> WHEN <othertest> THEN <returnthis> ELSE <returndefaultcase> END AS <newcolumnname> FROM <table>
You can even put case statements in an order by clause for really fancy ordering.
From SQL Server 2012 you can use the
IIF function for this.
SELECT IIF(Obsolete = 'N' OR InStock = 'Y', 1, 0) AS Salable, * FROM Product
This is effectively just a shorthand (albeit not standard SQL) way of writing
I prefer the conciseness when compared with the expanded
CASE resolve as expressions within a SQL statement and can only be used in well-defined places.
The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures.
If your needs can not be satisfied by these limitations (for example, a need to return differently shaped result sets dependent on some condition) then SQL Server does also have a procedural
IF @IncludeExtendedInformation = 1 BEGIN SELECT A,B,C,X,Y,Z FROM T END ELSE BEGIN SELECT A,B,C FROM T END
Use CASE. Something like this.
SELECT Salable = CASE Obsolete WHEN 'N' THEN 1 ELSE 0 END
SELECT (CASE WHEN (Obsolete = 'N' OR InStock = 'Y') THEN 'YES' ELSE 'NO' END) as Salable , * FROM Product