MySQL – How to Create Stored Procedure

If you are beginner with MySQL, you will face this particular issue while creating Stored Procedure. When I started with MySQL I had faced the similar situation as well. In MySQL we end every statement with semicolon. However, when we try to create stored procedure we face dilemmas that how to differentiate statement within stored procedure and end of the stored procedure itself.

To avoid this confusion in MySQL, before you create stored procedure you have to change the delimiter to something else and after stored procedure you have to reset it back to original delimiter.

Here is the example –

-- Create Stored procedure
-- SELECT Database
USE test;
-- Change Delimiter to //
DELIMITER //
CREATE PROCEDURE MySQLSP (var INT)
BEGIN
SELECT var;
END;
//
-- Change Delimiter to ;
DELIMITER ;
-- Call Stored Procedure
CALL MySQLSP(9);
-- Drop Stored Procedures
DROP PROCEDURE MySQLSP;

I have executed stored procedure with the value 9 as a parameter and it will return the value 9 as a result.

Reference: Pinal Dave (https://blog.sqlauthority.com)