How to Work with a Stored Procedure

DELIMITER //

CREATE PROCEDURE `p2` ()
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'A procedure'
BEGIN
    SELECT 'Hello World !';
END//

The four characteristics of a procedure are:

Language : For portability purposes; the default value is SQL. Deterministic : If the procedure always returns the same results, given the same input. This is for replication and logging purposes. The default value is NOT DETERMINISTIC. SQL Security : At call time, check privileges of the user. INVOKER is the user who calls the procedure. DEFINER is the creator of the procedure. The default value is DEFINER. Comment : For documentation purposes; the default value is ""

Calling a Stored Procedure

CALL stored_procedure_name (param1, param2, ....)   
ex:
 CALL procedure1(10 , 'string parameter' , @parameter_var); 

Delete a Stored Procedure

DROP PROCEDURE IF EXISTS p2;  

Parameters Lets examine how you can define parameters within a stored procedure.

CREATE PROCEDURE proc1 () : Parameter list is empty CREATE PROCEDURE proc1 (IN varname DATA-TYPE) : One input parameter. The word IN is optional because parameters are IN (input) by default. CREATE PROCEDURE proc1 (OUT varname DATA-TYPE) : One output parameter. CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE) : One parameter which is both input and output.

example 1) IN PARAM:

DELIMITER //  
CREATE PROCEDURE `proc_IN` (IN var1 INT)  
BEGIN  
SELECT var1 + 2 AS result;  
END//  

example 2) OUT PARAM:

DELIMITER //  
CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100))  
BEGIN  
SET var1 = 'This is a test';  
END //

example 3) INOUT PARAM:

DELIMITER //  
CREATE PROCEDURE `proc_INOUT` (OUT var1 INT)  
BEGIN  
SET var1 = var1 * 2;  
END //  

Working with variables:

DELIMITER //  
CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20))  
BEGIN  
DECLARE a, b INT DEFAULT 5;  
DECLARE str VARCHAR(50);  
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;  
DECLARE v1, v2, v3 TINYINT;      
INSERT INTO table1 VALUES (a);  
SET str = 'I am a string';  
SELECT CONCAT(str,paramstr), today FROM table2 WHERE b >=5;   
END //  

Flow Control Structures 1) IF statement

DELIMITER //  

CREATE PROCEDURE `proc_IF` (IN param1 INT)  
BEGIN  
DECLARE variable1 INT;  
SET variable1 = param1 + 1;  

IF variable1 = 0 THEN  
    SELECT variable1;  
END IF;  

IF param1 = 0 THEN  
    SELECT 'Parameter value = 0';  
ELSE  
    SELECT 'Parameter value <> 0';  
END IF;  
END //  

2) CASE statement

DELIMITER //  

CREATE PROCEDURE `proc_CASE` (IN param1 INT)  
BEGIN  
DECLARE variable1 INT;  
SET variable1 = param1 + 1;  

CASE   
WHEN variable1 = 0 THEN  
INSERT INTO table1 VALUES (param1);  
WHEN variable1 = 1 THEN  
INSERT INTO table1 VALUES (variable1);   
ELSE  
INSERT INTO table1 VALUES (99);  
END CASE;  

END //