Can You Have If-Then-Else Logic in SQL

Can you have if-then-else logic in SQL?

You can make the following sql query

IF ((SELECT COUNT(*) FROM table1 WHERE project = 1) > 0) 
    SELECT product, price FROM table1 WHERE project = 1
ELSE IF ((SELECT COUNT(*) FROM table1 WHERE project = 2) > 0) 
    SELECT product, price FROM table1 WHERE project = 2
ELSE IF ((SELECT COUNT(*) FROM table1 WHERE project = 3) > 0)
    SELECT product, price FROM table1 WHERE project = 3

How do I perform an IF...THEN in an SQL SELECT?

The 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 use the CAST operator 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.

SQL Server Denali (SQL Server 2012) adds the IIF statement which is also available in access (pointed out by Martin Smith):

SELECT IIF(Obsolete = 'N' or InStock = 'Y', 1, 0) as Saleable, * FROM Product

SQL Server INLINE IF ELSE

SQL Server does not have an inline if statement, but it does have an inline case that can be use to accomplish the same.

Case has two forms, one is:

select 
 case MyFlag 
   when 1 then 'YES'
   when 0 then 'NO'
   else 'OOPS'
 end
from MyTable

where it's used just like a switch in C-like languages and the other is:

select 
 case 
   when MyFlag = 1 then 'YES'
   when MyFlag = 0 then 'NO'
   -- when some unrelated condition...
   else 'OOPS'
 end
from MyTable

where it senquentially evaluates a list of conditions and returns the first that is fulfiled.

P.S. The end part is mandatory, and I usually forget that.
It's also usual for a simple case stament to be completely inlined, like

select (case MyFlag when 1 then 'Yes' else 'No' end) as MyFlagDesc

SQL: IF clause within WHERE clause

Use a CASE 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 points out.

SQL Server IF EXISTS THEN 1 ELSE 2

If you want to do it this way then this is the syntax you're after;

IF EXISTS (SELECT * FROM tblGLUserAccess WHERE GLUserName ='xxxxxxxx') 
BEGIN
   SELECT 1 
END
ELSE
BEGIN
    SELECT 2
END

You don't strictly need the BEGIN..END statements but it's probably best to get into that habit from the beginning.

if then statement in sql

Try this :

 declare @num int;
 set @num = 10;

 if (@num < 10)
 begin
   print('hello');
   set @num = @num +1;
 end
 else 
    begin
      print('over');
      set @num = @num +1;
    end

Using If else in SQL Select statement

you can use CASE

SELECT   ...,
         CASE WHEN IDParent < 1 THEN ID ELSE IDPArent END AS ColumnName,
         ...
FROM     tableName

---

Related Topics