Conditional Statements in MySQL

Handling MySQL Events with Triggers and Procedures Using SQL – Part 11

Division 5

Forward: In this part of the series, we look at conditional SQL statements in MySQL. We are dealing with MySQL 5.1.

By: Chrysanthus Date Published: 7 Aug 2012

Introduction

This is part 11 of my series, Handling MySQL Events with Triggers and Stored Procedures Using SQL. This series is the fifth division of my larger series, Database. You must have read all the different parts of the big series (or equivalent tutorials from elsewhere) up to this point. In this part of the series, we look at conditional SQL statements in MySQL. We are dealing with MySQL 5.1.

Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.

Illustration
If you do not eat then you will die of hunger. This conditional statement starts with the important, word, IF. After that you have the condition, which is the phrase, “you do not eat”. After, you have the important word, THEN. After you have what happens, which is “you would die of hunger”. You can have more than one thing that would happen, as in: If you do not eat, then you will die of hunger and you will be buried. In this second conditional statement, two things happen, which are, “you will die of hunger” and “you will be buried”.

There is an IF SQL statement, which does a very similar thing to the language conditional.

Basic Form of IF Statement
The syntax of the basic form of the IF statement is:

IF search-condition THEN
-- SQL statements
END IF;

So you have the IF reserved word, after you have the reserved word, THEN. After you have at least one SQL statement that must be executed if the search-condition is true. After you have the phrase, “END IF;”, that marks the end of the IF statement. If the search-condition is false, then no SQL statement between THEN and END IF will be executed. The group of statements between THEN and END IF is called a block. Note the semicolon at the end of END IF

You will be using the IF construct in procedures. So the examples in this tutorial use procedures.

Example
Run (type and click Enter) this code:

Delimiter //
CREATE PROCEDURE proex ()
BEGIN
    DECLARE myVar INTEGER DEFAULT 25;
    IF myVar = 25 THEN
        SELECT * FROM Invoice;
    END IF;
END;
//

The above code (IF block) should be executed. A procedure is something that can be executed over and over again. To see if the block was truly executed, run the following CALL statement;

CALL proex();

You should see the result of the SELECT statement indicating the IF Block has been executed.

The following IF block (between THEN and END IF) will not be executed because the search-condition is false:

Delimiter //
CREATE PROCEDURE proex ()
BEGIN
    DECLARE hisVar INTEGER DEFAULT 10;
    IF hisVar = 25 THEN
        SELECT * FROM Invoice;
    END IF;
END;
//

IF … THEN … ELSE
You have another situation where if the search-condition is true, then one block of SQL statements should be executed and if the search-condition is not true, a different block will be executed. The syntax is:

IF search-condition THEN
    -- Execute this block is true
ELSE
    -- Execute this block if false
END IF;

So if the condition is true, the first block is executed. If the condition is false the second block is executed. The ELSE reserved word makes it possible for the alternative (second) block to be executed if the search condition is false. The long IF statement here ends with “END IF” and a semicolon.

You can try the following code (call procedure after running), in which the alternative block is executed:

Delimiter //
CREATE PROCEDURE proexam ()
BEGIN

    DECLARE theVar INTEGER DEFAULT 10;

    IF theVar = 25 THEN
        SELECT * FROM Invoice;
    ELSE
        SELECT * FROM InvoiceDetails;
    END IF;

END;
//

One Block from a SET of Blocks
You may have many blocks where each block has its own search-condition and you expect only one block to be executed. In that case you have to use the following structure:

IF search-condition1 THEN
    -- Block 1
ELSEIF search-condition2 THEN
    -- Block 2
ELSEIF search-condition3 THEN
    -- Block 3
    - - -
ELSE
    -- Default Block
END IF;

In this structure, only one search-condition can turn out to be true and its block will be executed. Note the use of the reserved word, ELSEIF. The ELSE (last) block here does not have any search-condition. This block is not obligatory, but you can use it as the default block in case none of the blocks above is executed. If the ELSE block is there, then it will be executed if none of the blocks above is executed; that is, it will be executed if none of the search-conditions above is true. If the ELSE block is not included and none of the above search-condition is true, no block will be executed.

Read and run the following example:
Delimiter //
CREATE PROCEDURE proce ()
BEGIN

    DECLARE yourVar INTEGER DEFAULT 100;

    IF yourVar = 10 THEN
        SELECT * FROM Sales;
    ELSEIF yourVar = 20 THEN
        SELECT * FROM Orders;
    ELSEIF yourVar = 30 THEN
        SELECT * FROM Employee;
    ELSE
        SELECT * FROM Products;
    END IF;

END;
//

To see which block was executed, run the following:

CALL proce();

The ELSE block should have been executed.

That is it for this part of the series. Let us stop here and continue in the next part.

Chrys

Broad Network

Related Articles

Conditional Statements in MySQL

Handling MySQL Events with Triggers and Procedures Using SQL – Part 11

Division 5

Introduction

Related Links

Comments