MySQL IF Statement

The MySQL IF statement allows you to execute a set of SQL statements based on a certain condition or value of an expression. To form an expression in MySQL, you can combine literals, variables, operators, and even functions. An expression can return three value TRUE, FALSE or NULL.

MySQL IF statement syntax

The following illustrates the syntax of the IF statement:

IF if_expression THEN commands [ELSEIF elseif_expression THEN commands] [ELSE commands] END IF;

1 2 3 4

IF if_expression THEN commands    [ELSEIF elseif_expression THEN commands]    [ELSE commands]    END IF;

If the if_expression evaluates to TRUE the commands in the IF branch will execute. If it evaluates to FALSE, MySQL will check the elseif_expression and execute the commands in ELSEIF branch if the elseif_expression evaluates to TRUE.
The IF statement may have multiple ELSEIF branches to check multiple expressions.  If no expression evaluates to TRUE, the commands in the ELSE branch will execute.

MySQL IF statement examples

Let’s take a look at an example of how to use MySQL IF statements.

DELIMITER $$ CREATE PROCEDURE GetCustomerLevel( in p_customerNumber int(11), out p_customerLevel varchar(10)) BEGIN DECLARE creditlim double; SELECT creditlimit INTO creditlim FROM customers WHERE customerNumber = p_customerNumber; IF creditlim > 50000 THEN SET p_customerLevel = 'PLATINUM'; ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THEN SET p_customerLevel = 'GOLD'; ELSEIF creditlim < 10000 THEN SET p_customerLevel = 'SILVER'; END IF; END$$

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

DELIMITER $$   CREATE PROCEDURE GetCustomerLevel(     in  p_customerNumber int(11),     out p_customerLevel  varchar(10)) BEGIN     DECLARE creditlim double;       SELECT creditlimit INTO creditlim     FROM customers     WHERE customerNumber = p_customerNumber;       IF creditlim > 50000 THEN     SET p_customerLevel = 'PLATINUM';     ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THEN         SET p_customerLevel = 'GOLD';     ELSEIF creditlim < 10000 THEN         SET p_customerLevel = 'SILVER';     END IF;   END$$

We pass customer number to the stored procedure to get customer level based on credit limit. We use IF ELSEIF and ELSE statement to check customer credit limit against multiple values.
The following is the flow chart  that demonstrates the logic of determining customer level.

In this tutorial, you have learned how to use MySQL IF statement to execute a block of SQL code based on conditions.

Introduction to MySQL stored procedure parameters

Almost stored procedures that you develop require parameters. The parameters make the stored procedure more flexible and useful. In MySQL, a parameter has one of three modes IN, OUT or INOUT.

• IN – is the default mode. When you define an IN parameter in a stored procedure, the calling program has to pass an argument to the stored procedure. In addition, the value of an IN parameter is protected. It means that even the value of the IN parameter is changed inside the stored procedure, its original value is retained after the stored procedure ends. In other words, the stored procedure only works on the copy of the IN parameter.
• OUT – the value of an OUT parameter can be changed inside the stored procedure and its new value is passed back to the calling program. Notice that the stored procedure cannot access the initial value of the OUT parameter when it starts.
• INOUT – an INOUT parameter is the combination of IN parameter and OUT parameter. It means that the calling program may pass the argument, and the stored procedure can modify the INOUT parameter and pass the new value back to the calling program.

The syntax of defining a parameter in the stored procedures is as follows:

MODE param_name param_type(param_size)

1	MODE param_name param_type(param_size)

• The MODE could be IN, OUT or INOUT , depending on the purpose of parameter in the stored procedure.
• The param_name is the name of the parameter. The name of parameter must follow the naming rules of the column name in MySQL.
• Followed the parameter name is its data type and size. Like a variable, the data type of the parameter can by any MySQL data type.

Each parameter is separated by a comma ( ;) if the stored procedure has more than one parameter.
Let’s practice with some examples to get a better understanding.

MySQL stored procedure parameter examples

IN parameter example

The following example illustrates how to use the IN parameter in the GetOfficeByCountry stored procedure that selects offices located in a specified country.

DELIMITER // CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255)) BEGIN SELECT * FROM offices WHERE country = countryName; END // DELIMITER ;

1 2 3 4 5 6 7 8

DELIMITER // CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))     BEGIN         SELECT *         FROM offices         WHERE country = countryName;     END // DELIMITER ;

The countryName is the IN parameter of the stored procedure. Inside the stored procedure, we select all offices that locate in the country specified by the countryName parameter.
Suppose, you want to get all offices in the USA, you just need to pass a value (USA) to the stored procedure as follows:

CALL GetOfficeByCountry('USA')

1	CALL GetOfficeByCountry('USA')

To get all offices in France, you pass the France literal string to the GetOfficeByCountry stored procedure as follows:

CALL GetOfficeByCountry('France')

1	CALL GetOfficeByCountry('France')

OUT parameter example

The following stored procedure returns the number of orders by order status. It has two parameters:

• orderStatus: IN parameter that is the order status which you want to count the orders.
• total: OUT parameter that stores the number of orders for a specific order status.

The following is the source code of the CountOrderByStatus stored procedure.

DELIMITER $$ CREATE PROCEDURE CountOrderByStatus( IN orderStatus VARCHAR(25), OUT total INT) BEGIN SELECT count(orderNumber) INTO total FROM orders WHERE status = orderStatus; END$$ DELIMITER ;

1 2 3 4 5 6 7 8 9 10 11

DELIMITER $$ CREATE PROCEDURE CountOrderByStatus(         IN orderStatus VARCHAR(25),         OUT total INT) BEGIN     SELECT count(orderNumber)     INTO total     FROM orders     WHERE status = orderStatus; END$$ DELIMITER ;

To get the number of shipped orders, we call the CountOrderByStatus stored procedure and pass the order status as Shipped, and also pass an argument ( @total) to get the return value.

CALL CountOrderByStatus('Shipped',@total); SELECT @total;

1 2 3

CALL CountOrderByStatus('Shipped',@total);   SELECT @total;

To get the number of orders that are in process, we call the CountOrderByStatus stored procedure as follows:

CALL CountOrderByStatus('in process',@total); SELECT @total AS total_in_process;

1 2 3

CALL CountOrderByStatus('in process',@total);   SELECT @total AS  total_in_process;

INOUT parameter example

The following example demonstrates how to use INOUT parameter in the stored procedure.

DELIMITER $$ CREATE PROCEDURE set_counter(INOUT count INT(4),IN inc INT(4)) BEGIN SET count = count + inc; END$$ DELIMITER ;

1 2 3 4 5 6

DELIMITER $$ CREATE PROCEDURE set_counter(INOUT count INT(4),IN inc INT(4)) BEGIN     SET count = count + inc; END$$ DELIMITER ;

How it works.

• The set_counter stored procedure accepts one INOUT parameter ( count) and one IN parameter ( inc).
• Inside the stored procedure, we increase the counter ( count) by the value of the inc parameter.

See how we call the set_counter stored procedure:

SET @counter = 1; CALL set_counter(@counter,1); -- 2 CALL set_counter(@counter,1); -- 3 CALL set_counter(@counter,5); -- 8 SELECT @counter; -- 8

1 2 3 4 5

SET @counter = 1; CALL set_counter(@counter,1); -- 2 CALL set_counter(@counter,1); -- 3 CALL set_counter(@counter,5); -- 8 SELECT @counter; -- 8

In this tutorial, we have shown you how to define parameters in stored procedures, and introduced you to different parameter modes including IN, OUT and INOUT.

MySQL Stored Procedure Variables

Declaring variables

To declare a variable inside a stored procedure, you use the DECLARE statement as follows:

DECLARE variable_name datatype(size) DEFAULT default_value;

1	DECLARE variable_name datatype(size) DEFAULT default_value;

Let’s examine the statement above in more detail:

• First, you specify the variable name after the  DECLARE keyword. The variable name must follow the naming rules of MySQL table column names.
• Second, you specify the data type of the variable and its size. A variable can have any MySQL data types such as INT, VARCHAR, DATETIME, etc.
• Third, when you declare a variable, its initial value is NULL. You can assign the variable a default value by using DEFAULT keyword.

For example, we can declare a variable named  total_sale with the data type INT and default value 0 as follows:

DECLARE total_sale INT DEFAULT 0

1	DECLARE total_sale INT DEFAULT 0

MySQL allows you to declare two or more variables that share the same data type using a single DECLARE statement as following:

DECLARE x, y INT DEFAULT 0

1	DECLARE x, y INT DEFAULT 0

We declared two INT variables  x and  y , and set their default values to zero.

Assigning variables

Once you declared a variable, you can start using it. To assign a variable another value, you use the SET statement, for example:

DECLARE total_count INT DEFAULT 0 SET total_count = 10;

1 2	DECLARE total_count INT DEFAULT 0 SET total_count = 10;

The value of the total_count variable is 10 after the assignment.
Besides the SET statement, you can use SELECT INTO statement to assign the result of a query to a variable. Notice that the query must return a scalar value.

DECLARE total_products INT DEFAULT 0 SELECT COUNT(*) INTO total_products FROM products

1 2 3 4

DECLARE total_products INT DEFAULT 0   SELECT COUNT(*) INTO total_products FROM products

In the example above:

• First, we declare a variable named total_products and initialize its value to 0.
• Then, we used the SELECT INTO statement to assign the total_products variable the number of products that we selected from the products from the products table.

Variables scope

A variable has its own scope, which defines its life time. If you declare a variable inside a stored procedure, it will be out of scope when the END statement of stored procedure reached.
If you declare a variable inside BEGIN END block, it will be out of scope if the END is reached. You can declare two or more variables with the same name in different scopes because a variable is only effective in its own scope. However, declaring variables with the same name in different scopes is not good programming practice.
A variable that begins with the @ sign at the beginning is session variable. It is available and accessible until the session ends.

Calling stored procedures

CALL STORED_PROCEDURE_NAME()

You use the CALL statement to call a stored procedure e.g., to call the GetAllProducts stored procedure, you use the following statement:

CALL GetAllProducts();

1	CALL GetAllProducts();

Summary: in this tutorial, we will show you step by step how to develop the first MySQL stored procedure by using CREATE PROCEDURE statement. In addition, we will show you how to call the stored procedures from SQL statements.

Writing the first MySQL stored procedure

We are going to develop a simple stored procedure named GetAllProducts() to help you get familiar with the syntax. The GetAllProducts() stored procedure selects all products from the products table.

DELIMITER //

CREATE PROCEDURE GetAllProducts()

   BEGIN

   SELECT *  FROM products;

   END //

DELIMITER ;

Home / MySQL Stored Procedure / Getting Started with MySQL Stored Procedures

Getting Started with MySQL Stored Procedures

Summary: in this tutorial, we will show you step by step how to develop the first MySQL stored procedure by using CREATE PROCEDURE statement. In addition, we will show you how to call the stored procedures from SQL statements.

Writing the first MySQL stored procedure

We are going to develop a simple stored procedure named GetAllProducts() to help you get familiar with the syntax. The GetAllProducts() stored procedure selects all products from the products table.
Launch the mysql client tool and type the following commands:

DELIMITER // CREATE PROCEDURE GetAllProducts() BEGIN SELECT * FROM products; END // DELIMITER ;

1 2 3 4 5 6

DELIMITER // CREATE PROCEDURE GetAllProducts()    BEGIN    SELECT *  FROM products;    END // DELIMITER ;

Let’s examine the stored procedure in greater detail:

• The first command is DELIMITER //, which is not related to the stored procedure syntax. The DELIMITER statement changes the standard delimiter which is semicolon ( ;) to another. In this case, the delimiter is changed from the semicolon( ;) to double-slashes //. Why do we have to change the delimiter? Because we want to pass the  stored procedure to the server as a whole rather than letting mysql tool to interpret each statement at a time.  Following the END keyword, we use the delimiter // to indicate the end of the stored procedure. The last command ( DELIMITER;) changes the delimiter back to the standard one.
• We use the CREATE PROCEDURE statement to create a new stored procedure. We specify the name of stored procedure after the CREATE PROCEDURE statement. In this case, the name of the stored procedure is GetAllProducts. We put the parentheses after the name of the stored procedure.
  
• The section between BEGIN and END is called the body of the stored procedure. You put the declarative SQL statements in the body to handle business logic. In this stored procedure, we use a simple SELECT statement to query data from the products table.

What are ENUMs used for in MySQL?

You can limit the possible values that go into the table. CREATE TABLE months (month ENUM ‘January’, ‘February’, ‘March’,…); INSERT months VALUES (’April’);

How are ENUMs and SETs represented internally?

ENUMs and SETs are used to represent powers of two because of storage optimizations.