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)
2 Comments. Leave new
That is crazy! Imagine if one DDL script which doesn’t reset the batch delimiter back to a semi colon slips through to a production environment. Mayhem will ensue.
Hey Oracle, what’s up with “GO”?!
In MySQL stored procedures are compiled on demand. So application uses a stored procedure multiple times in a single connection it uses the compiled code or else its works like a normal query
then,
what is the use of stored procedure in MySQL?