MySQL: Creating a simple stored procedure

Let’s start creating stored procedure with a very simple one and then we will analyse it line by line.




  • By default MySQL treats semicolon(;) as the statement terminator or end of statement. But as we are going to use it inside the procedure body, so we need another different delimiter to state the end of the stored procedure. DELIMITER $$ sets $$ as the statement terminator.
  •  The DROP PROCEDURE IF EXISTS <SPName> statement checks for a duplicate stored procedure with the same name and if there exists any then issue a DROP command. You can skip this line if you are sure that there is no other stored procedure exists with the same name in your selected database.
  • Statement CREATE PROCEDURE marks the start of the stored procedure definition. Here, MyFirstSP is the name of our stored procedure. The stored procedure name followed by a pair of parentheses. The use of these parentheses is to define parameters inside it. In this stored procedure we don’t need any parameters, but we have to put these parentheses as this is mandatory in MySQL unlike SQL Server.
  • The BEGIN Statement marks the start or begining of a block (here the block is the stored procedure itself).In stored procedures, every statements with multiple statements should be enclosed with a block defined by BEGIN and END, where END statement marks the end of the block;
  •  The statement inside the BEGIN .. END is a simple SELECT query, which fetches all the records from MyTable table.


7 thoughts on “MySQL: Creating a simple stored procedure

  1. @RedHatSource, I already have mentioned about this in the post. Please go through the first point in Analysis section and if you still need any further explanation please let me know.

    Thanks for visiting the post🙂

  2. Pingback: MySQL: Condition based sorting | Technology Talks

  3. Pingback: MySQL: Condition based sorting – StdGod

Leave a Reply

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

You are commenting using your 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