Example of Create Procedure in MySQL

Syntax of creating the Procedure:
IN parameter: used from insert/pass the value through procedure parameter.
OUT Parameter: used for get the value from Procedure parameter.
IN/OUT parameter: used for both purpose.

-- Create the Procedure.
DELIMITER &&  
CREATE PROCEDURE procedure_name([[IN | OUT | INOUT] parameter_name datatype [, ....] ])    
BEGIN    
    Declaration_section    
    Executable_section    
END &&  

--Execute the Procedure.
CALL procedure_name(Parameters);

Example of Creating & Executing the Procedure with all three parameters:

-- Create the Procedure
DELIMITER $$
DROP PROCEDURE IF EXISTS sp_example;
CREATE PROCEDURE sp_example(IN x INT, OUT y INT,INOUT z INT)
BEGIN
SET y = x + 1;
SET z = y + 1;
END $$
DELIMITER ;

-- Execute the sp_example procedure:
SET @x = 10;
SET @z = 30;
call sp_example( @x,@y, @z);
SELECT @x, @y, @z;

mysql> SET @x = 10;
Query OK, 0 rows affected (0.01 sec)

mysql> SET @z = 30;
Query OK, 0 rows affected (0.00 sec)

mysql> call sp_example( @x,@y, @z);
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @x, @y, @z;
+------+------+------+
| @x   | @y   | @z   |
+------+------+------+
|   10 |   11 |   12 |
+------+------+------+
1 row in set (0.00 sec)

Example of Creating Procedure with SELECT statement:

--Create the procedure with select statement 
DELIMITER &&  
CREATE PROCEDURE sp_example_out (OUT v_out_id INT)  
BEGIN  
    SELECT MAX(id) INTO v_out_id FROM city;   
END &&  
DELIMITER ; 

-- Execute the sp_example_out procedure
call sp_example_out(@y);
select @y;

mysql> call sp_example_out(@y);
Query OK, 1 row affected (0.01 sec)

mysql> select @y;
+------+
| @y   |
+------+
| 4079 |
+------+
1 row in set (0.00 sec)

Example of select statement with in variable:

--Create the stored procedure
DELIMITER &&  
CREATE PROCEDURE p_example_in (IN v_in_rowslimit INT)  
BEGIN  
    SELECT * FROM city LIMIT v_in_rowslimit;     
END &&  
DELIMITER ;  

-- Execute the p_example_in procedure:
SET @v=3;
call p_example_in(@v);

mysql> SET @v=3;
Query OK, 0 rows affected (0.00 sec)

mysql> call p_example_in(@v);
+----+----------+-------------+----------+------------+
| ID | Name     | CountryCode | District | Population |
+----+----------+-------------+----------+------------+
|  1 | Kabul    | AFG         | Kabol    |    1780000 |
|  2 | Qandahar | AFG         | Qandahar |     237500 |
|  3 | Herat    | AFG         | Herat    |     186800 |
+----+----------+-------------+----------+------------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Check Procedure present in MySQL

Syntax:
SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE search_condition]  

Example
SHOW PROCEDURE STATUS WHERE db = 'TEST_BKP';  

Check the Procedure DDL

SHOW CREATE PROCEDURE p_example_in \G;  

mysql> SHOW CREATE PROCEDURE p_example_in \G;
*************************** 1. row ***************************
           Procedure: p_example_in
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `p_example_in`(IN v_in_rowslimit INT)
BEGIN
    SELECT * FROM city LIMIT v_in_rowslimit;
END
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

ERROR:
No query specified

Drop the procedure

DROP PROCEDURE [ IF EXISTS ] procedure_name; 
Example:
DROP PROCEDURE p_example_in;