MySQL Stored Procedure With Parameters

This tutorial educates about MySQL stored procedures and demonstrates how we can create them with and without parameters.

MySQL Stored Procedure With Parameters

A stored procedure is a subprogram or a block of code that you define to perform a specific task; they are similar to functions in other programming languages.

The procedures are stored in a relational database and can be called and used multiple times. You have to assign a name while creating a stored procedure.

Moreover, you can also provide parameters to it. Below is the syntax for creating a MySQL stored procedure.

Syntax to Create MySQL Stored Procedure

CREATE
    [DEFINER = user]
    PROCEDURE [IF  NOT  EXISTS] procedure_name  ([procedure_parameters[,...]])
    [characteristic  ...]  routine_body
    procedure_parameters: [ IN | OUT | INOUT ] parameters_name type
    type : _Any valid MySQL _data type
    characteristic: {
    COMMENT  '_string_'
    | LANGUAGE  SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL  DATA | MODIFIES SQL  DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    }
routine_body: Valid SQL routine statement

If you find the above syntax overwhelming, you can refer to the simpler version of the above syntax given below.

DELIMITER {custom delimiter}
CREATE PROCEDURE procedure_name([IN | OUT | INOUT] param_1 [DATATYPE], ...)
BEGIN
// Your Code
END;
{custom delimiter}

Call MySQL Stored Procedure

We can use the following command to call the MySQL stored procedure once it is created.

call procedure_name(param_1, param2,...);

Keep the following points at your fingertips while creating a stored procedure in MySQL:

1. Use the CREATE PROCEDURE command to create a MySQL stored procedure followed by the procedure-name. The procedure names are case-insensitive, which means new_proc and NEW_PROC are the same.
2. A database can’t have two procedures with the same name.
3. The names of procedures can be delimited. If the name is delimited, spaces are permitted.
4. A procedure name must not exceed 64 characters.
5. Do not use MySQL’s default built-in function names as procedure names; otherwise, it will lead to an error.
6. A pair of parenthesis () make up the final component of CREATE PROCEDURE. Parentheses contain parameters; if there are no parameters, then use empty parentheses.
7. The next section is where the statements are written.
8. Specify the parameters as OUT, IN, or INOUT, which is valid only for procedures. We must follow this step if you want to pass parameters.

the IN Parameter in MySQL Stored Procedure

The IN parameter passes the value to a stored procedure.

DELIMITER //
CREATE PROCEDURE simple_addition (IN param_1 INT, IN param_2 INT)
BEGIN
    SELECT param_1 + param_2;
END //
DELIMITER ;

CALL simple_addition(2,4); #produces 6 as output

OUTPUT:

param_1 + param_2
6

the OUT Parameter in MySQL Stored Procedure

We can update the OUT parameter’s value within the stored procedure, which is further passed back to a calling program.

DELIMITER //
CREATE PROCEDURE simple_addition (IN param_1 INT, IN param_2 INT, OUT param_3 INT)
BEGIN
    SET param_3 = param_1 + param_2;
END //
DELIMITER ;

CALL simple_addition(2,4, @output_var);
SELECT @output_var;

OUTPUT:

@output_var
6

the INOUT Parameter in MySQL Stored Procedure

An INOUT parameter combines IN and OUT parameters. The calling program can pass an argument, which can be modified by the stored procedure and returns the new value to the calling program.

DELIMITER //
CREATE PROCEDURE counter(INOUT set_counter INT, IN increment INT)
BEGIN
    SET set_counter = set_counter + increment;
END //
DELIMITER ;

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

OUTPUT:

@counter
8

Here, we are using increment as an input parameter while set_counter is an input and an output parameter.

Characteristics of MySQL Stored Procedure

MySQL gives you access to describe the characteristics of the procedure you are creating.

characteristic: {
    COMMENT  '_string_'
    | LANGUAGE  SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL  DATA | MODIFIES SQL  DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    }

These characteristics are followed by the parentheses and preceded by the BEGIN clause. However, these characteristics are optional; you do not have to write these lines of code every time explicitly.

All the above values of characteristic clauses are on their default. For example, the following two stored procedures produce the same output.

DELIMITER //
CREATE PROCEDURE procedure_name ()
BEGIN
    SELECT CURRENT_DATE();
END //
DELIMITER ;

call procedure_name(); -- 2022-09-05

The above code is the same as when characteristics are mentioned explicitly.

DELIMITER //
CREATE PROCEDURE procedure_name ()
COMMENT ''
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
    SELECT CURRENT_DATE();
END //
DELIMITER ;

call procedure_name(); -- 2022-09-05

So, we have learned how to use parameters with procedures in MySQL.