Most (probably all) programming languages include some form of the IF statement that allows programmers to write conditional code. That is, code that will execute only if a certain condition is true.

It’s a very simple concept. Basically it goes like this:

“If this, do that.”

Most languages simply call it IF, but some have their own twist on the name (for example, in ColdFusion/CFML, it’s called CFIF).

In any case, they essentially do the same thing.

In SQL Server (or more precisely, its programming language T-SQL) it’s called IF.

Example

Here’s a basic example to demonstrate how the IF statement works.

IF 1 < 2 SELECT 'True';

Result:

True

In this case, the expression to evaluate is 1 < 2. It is true that 1 is less than 2 so the SELECT statement was run and True was returned.

However, if the expression is false, nothing will happen.

The following example demonstrates this:

IF 1 > 2 SELECT 'True';

Result:

Commands completed successfully.

All SQL Server told me is that the command completed successfully. Nothing else was returned, because nothing else was specified.

The IF… ELSE Statement

In SQL Server, the IF statement comes with an optional ELSE, which allows you to provide an alternative, in the event the expression returns false.

Therefore, it’s like saying “IF this, do this, ELSE, do this”.

In this case, we can add the optional ELSE to the IF statement, so that it becomes an IF... ELSE statement.

IF 1 > 2
       SELECT 'True';
ELSE 
       SELECT 'False';

Result:

False

Now we also get a result when the expression is false.

Nested IF Statements

One of the great things about IF statements is that you can nest them. That is, you can add more IF statements inside your original one, and more inside those etc.

You can nest as many IF statements as you like. You’re only limited by the amount of available memory.

Here’s an example of nesting an IF statement inside another.

DECLARE @bankBalance int = 123;
IF @bankBalance > 100
    IF @bankBalance > 150
        SELECT 'Rich!';
    ELSE
        SELECT 'Wealthy';
ELSE 
    SELECT 'Poor';

Result:

Wealthy

IF… ELSE IF Statements

You can also use it as an IF... ELSE IF statement.

DECLARE @bankBalance int = 123;
IF @bankBalance > 150
    SELECT 'Rich!';
ELSE IF @bankBalance > 100
    SELECT 'Wealthy';
ELSE
    SELECT 'Poor';

Result:

Wealthy