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 (http://blog.sqlauthority.com)

About these ads

2 thoughts on “MySQL – How to Create Stored Procedure

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

  2. Pingback: MySQL – How to Create Stored Procedure in MySQL | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s