MySQL Conditional Statements

Programming in MySQL – Part 7

MySQL Course

Foreword: In this part of the series, I talk about MySQL conditional statements.

By: Chrysanthus Date Published: 27 May 2015

Introduction

This is part 7 of my series, Programming in MySQL. In this part of the series, I talk about MySQL conditional statements. You should have read the previous parts of the series before reaching here, as this is a continuation.

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 and triggers. The examples in this tutorial use procedures.

Start the command line tool and connect to the wholesale database with the following guidelines (the MySQL server should already be on):

cd c:\
Mysqlcom.pl (possibly in c:\Perl\lib directory)
connect "root", "localhost", 3306;
Password: sql
USE wholesale;

Example
Create the following procedure:

delimiter //
CREATE PROCEDURE proex (OUT par1 INTEGER)
BEGIN
    DECLARE myVar INTEGER DEFAULT 25;
    IF myVar = 25 THEN
        SET par1 = myVar;
    END IF;
END;//
delimiter ;

The procedure should have been saved. A procedure is something that can be executed over and over again. To see if the block operates properly, execute (type and press Enter) the following statements;

    SET @va1 = NULL;
    CALL proex(@va1);
    SELECT @va1;

You should see the result of the SELECT statement indicating that the IF Block has been executed. Remember, the SELECT statement displays whatever it gets to the output.

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

delimiter //
CREATE PROCEDURE pro (OUT par1 INTEGER)
BEGIN
    DECLARE myVar INTEGER DEFAULT 10;
    IF myVar = 25 THEN
        SET par1 = myVar;
    END IF;
END;//
delimiter ;

myVar is now 10 and not 25, so the search-condition is false.

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

IF search-condition THEN
    -- Execute this block if 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 (ELSE is not followed by a semicolon).

You can try the following code, in which the alternative block is executed:

delimiter //
CREATE PROCEDURE proexam (OUT pa INTEGER)
BEGIN

    DECLARE theVar INTEGER DEFAULT 10;

    IF theVar = 25 THEN
        SET pa = 1;
    ELSE
        SET pa = 0;
    END IF;

END;//
delimiter ;

SET @variab1 = NULL;
CALL proexam(@variab1);
SELECT @variab1;

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 try the following example:

delimiter //
CREATE PROCEDURE proce (OUT par INTEGER)
BEGIN

    DECLARE yourVar INTEGER DEFAULT 100;

    IF yourVar = 10 THEN
        SET par = 11;
    ELSEIF yourVar = 20 THEN
        SET par = 22;
    ELSEIF yourVar = 30 THEN
        SET par = 33;
    ELSE
        SET par = 99;
    END IF;

END;//
delimiter ;

To see which block was executed, run the following:

SET @vari1 = NULL;
CALL proce(@vari1);
SELECT @vari1;

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

MySQL Conditional Statements

Programming in MySQL – Part 7

MySQL Course

Introduction

Related Links

Comments