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)

MySQL, SQL Scripts
Previous Post
SQL SERVER – Optimal Memory Settings for SQL Server – Notes from the Field #006
Next Post
Personal Technology – How to Dress for an Interview – Guidelines and Suggestions

Related Posts

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”?!

    Reply
  • 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?

    Reply

Leave a Reply