This is not about how to write a Stored Procedure (SP), But how to efficiently write a SP.

MySQL Query Browser

The easiest way to write a SP is to use a MySQL Query Browser, Just select the database and right click the dB and “Create Stored Routite…” This shall help you do easily modify, edit and create procedures.

Command Line

Unfortunately, not many of the dB guys could have access to GUI and create/edit access permissions, they may have to rely on the CLI. This is where create / edit of SPs is the most tedious. Lets set something in our system, before we log into MySQL.

• In your ~/.bashrc add the following…

  export EDITOR=vim VISUAl=vim
  

• Create / Edit ~/.vimrc file, and add the following…

  set noai
  set nonu
  

• Log into the MySQL CLI, and set the following command

  delimiter //

The Developer now is ready on the server side to create/edit the procs. On the client side I assume the Developer has a Notepad equivalent text editor, or even better, I suggest Kate (if you are on Linux box).

In the editor…

• Write an if exists drop procedure statement…
• With few carriage returns following…, write the whole content (SP)
• If a specific user/s need execute, write the grant execute permission after few carriage returns…

The dev is now ready to Create the proc..

1. Paste the if exists drop statement and execute
2. In the mysql prompt, select the dB and enter the following prompt

   \e

   . This is basically to go into editor mode. Press “i”, to get into the insert mode and paste the actual SP Content

3. Press Escape key, to go back in normal mode and then press “:wq” to save and exit
4. Enter “//” (the command delimiter) and press Enter, that should either create the proc, or return with the errors.
5. Execute the Execute Grant statements

Done, Simple.

PS: Copy Paste @ rescue yet again !!! 😀