IF...ELSE conditional statement in Transact SQL

IF…ELSE are flow control structures that allow you to execute or skip a statement block based on a specified condition.

The IF statement is used to execute a code block if a condition is met TRUE. If a condition is not satisfied FALSE then the ELSE statement can optionally be used.

ELSE is generally used after IF statements, and validates that the corresponding statements are executed in case the IF conditions return FALSE. In other words, if the IF conditions are not met, the ELSE statements are executed.

The basic syntax is as follows:

IF condition
 Code to execute if the condition is true
ELSE
 Code to execute if the condition is false

To correctly understand how these flow control structures work, let’s see two examples:

IF..ELSE:

DECLARE @a INT = 5;
DECLARE @b INT = 10;

IF @a < @b
   SELECT @a
ELSE
   SELECT @b

In this simple example, we declare 2 numeric variables and check if the value of the first @a is less than the value of the second @b. In this case, since the condition is met, a query for @a will be made. If we modify the values and set @a higher than @b, we can check that our code executes the ELSE and will query for @b.

BEGIN
   DECLARE @id  INT;
   DECLARE @name NVARCHAR (50);
   DECLARE @lastName NVARCHAR (50);
   SET @id = 8;
   SET @name = 'Mavin';
   SET @lastName = 'Pettitt';
   DECLARE @address NVARCHAR (100) = 'Av. de América 33' ;
   Declare @bankAccount FLOAT = 678555432;

   IF (SELECT COUNT (1) existingCustomer
       FROM Customers
       WHERE customerId = @id) = 0
       BEGIN
           SET  IDENTITY_INSERT Customers  ON
           INSERT INTO Customers (customerId,
                               [name],
                               [lastName],
                               [address],
                               bankAccount)
                       VALUES (@id,
                               @name,
                               @lastName,
                               @address,
                               @bankAccount)
           SET  IDENTITY_INSERT Customers  OFF
           SELECT *
           FROM Customers
           WHERE customerId = @id
       END
   ELSE PRINT 'That identifier already exists in the customers table'
END

In this example we are working with our table: Customers. For those users less experienced in handling Transact SQL, we proceed to explain step by step how the code works:

1. First, declare and set several variables that correspond to the data stored in a record of the Clients table.

2. IF validates that the SELECT expression is TRUE or FALSE. The SELECT returns a COUNT of the number of records according to the value we gave the WHERE as a parameter. In this case we are giving it the variable @id which stores the value 8. An important fact to remember, the customerId corresponds to the Primary Key of the table, so its value cannot be repeated. This means that depending on the value we give to the SELECT, it can only return 0 or 1.

3. If the IF condition is met, meaning that it returns 0 = 0 (TRUE), it will indicate that the customerId that we have validated does not exist in the Customers table, so we can proceed to make an INSERT INTO of the new record with the values that we have saved in the variables. Before that, first set IDENTITY_INSERT to ON, so we can skip the IDENTITY property of customerId and insert the value we want, then do the INSERT INTO on the table, return to set IDENTITY_INSERT to OFF and perform a SELECT to display the new record in the Customers table.

4. In the event that the IF returns FALSE, that is, that the @id already exists as customerId in the table, our code will be executed by the ELSE showing a message indicating that this identifier already exists, so we cannot insert the record.

Nested IFS:

Nested IFs are standard IF that contain other independent If, so if the conditions of the first are met, the expressions of the other IF that it contains will also be validated. Within a code block nested IFs can indefinited be used and be nested into different levels, that means that a nested IF inside a main IF can also contain oither nested Ifs internally and so on.

DECLARE @a INT = 5
DECLARE @b INT = 10
DECLARE @c INT = 15
DECLARE @d INT = 20

IF @a <= @b
	IF @d > @c AND @c > @b
		IF @d = 20
		SELECT 'THE RESULT IS TRUE' AS RESULT
		ELSE
		SELECT 'THE SECOND NESTED IF IS FALSE' AS RESULT
	ELSE
		SELECT 'THE FIRST NESTED IF IS FALSE' AS RESULT
ELSE
	SELECT 'THE MAIN IF IS FALSE' AS RESULT

In the following example we see an IF that contais a nested IF and this in turn internally contains another nested IF. Depending on the value that we give to the variables used, we will see if the execution ends correctly or, failing that, the level where the expression returns false, executing the respective ELSE.