What Is MySQL Stored Procedure?

A stored procedure is a segment of declarative SQL statements stored inside the database catalog. A stored procedure can be invoked by triggers, other stored procedures, and applications such as Java, Python, PHP..
In another word Stored procedure is set of SQL code which is stored in the database server and can be invoked by program or trigger or stored procedure itself. Stored procedure is a way to execute many tasks on your database server directly.

Create Procedure Syntax:

Syntax : 
CREATE PROCEDURE procedure_name [ (parameter datatype [, parameter datatype]) ]
BEGIN
   declaration_section
   executable_section
END;

Ex : 
CREATE PROCEDURE GetAllEmployee()
 BEGIN
 SELECT * FROM Tbl_Employee; 
 END 
 ;

How to call Procedure?

CALL <name>;
Ex : CALL GetAllEmployee();

Sample example of create and call procedure
Try It Now

Procedure Parameter-

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

IN Parameter Example-

CREATE PROCEDURE `testProcedureInParameter` (IN var1 INT)
BEGIN
 SELECT var1 + 2 AS result;
END;
CALL testProcedureInParameter(10);
Try It Now

OUT Parameter Example-

CREATE PROCEDURE `testProcedureOutParameter` (OUT var1 VARCHAR(100))
BEGIN
 SET var1 = 'This Is a test OUT Parameter Procdure test';
END;
CALL testProcedureOutParameter(@var1);
Try It Now

INOUT Parameter Example-

SET @inoutvar = 10;
CREATE PROCEDURE `testProcedureINOutParameter` (INOUT inoutvar INT)
BEGIN
 SET inoutvar = inoutvar * 2;
END;
CALL testProcedureINOutParameter(@inoutvar);

SELECT @inoutvar;
Try It Now

Drop procedure using-

DROP PROCEDURE IF EXISTS <name>;
Ex : DROP PROCEDURE IF EXISTS GetAllEmployee;

Show procedure definition-

SHOW CREATE PROCEDURE <name>;
Ex : SHOW CREATE PROCEDURE GetAllEmployee;
Try It Now

Show list of procedure-

SHOW PROCEDURE STATUS;
Try It Now