Stored Procedures

A Stored Procedure (also a Stored Proc) is a sub-program which is physically stored within a database in compiled form under a certain name. This allows you to share the procedure between a number of programs.

Stored Procedures are implemented in the 5.0 version of MySQL Server, and you can work with them in full using SQLyog.

Create Stored Procedure

To create a new stored procedure, use the menu item Others -> Stored Procedure-> Create Stored Procedure or use the database context menu.

Enter the procedure name in the appeared dialog, and SQLyog will generate a template for your stored procedure in a separate tab of the SQL Window. Fill the template as necessary for you. Note that the template contains the command to drop the procedure before its creation is case it already exists. Also the most common options are displayed in this template. If you want to use the defaults just leave it commented out.

Use the toolbar buttons or hotkeys to execute the queries. As editor tabs of a special kind are used for stored procedures creation/alteration, pressing both F5 and Shift+F5 keys leads to execution of all queries present in the SQL window.

To create a useful Stored Procedure there must be some code in between the BEGIN .. END sequence, which can be a valid SQL statement. If there are more SQL statements here, then they should be separated by ";" (semicolon). Inside a Stored Procedure the delimiter is always ";", here it cannot be changed. This explains why the delimiter needs to be changed (to "$$") outside the Stored Procedure before creating it - if not SQLyog (or any client) would 'think' that the SQL statement stops after the first line after "BEGIN" rather than "END". When setting the DELIMITER to "$$" outside the Stored Procedure, SQLyog will understand that the complete CREATE PROCEDURE statement is a single statement.

Also note that when you are executing this example you only create the Stored Procedure (the code is stored in the database itself). To execute it you must use the CALL SQL-statement like 'call myproc()' or 'call myproc({parameter list})'.

Alter Stored Procedure

To alter an existing stored procedure, select it in the Object browser and use the menu item Others -> Stored Procedure -> Alter Stored Procedure or press F6.

A template for the procedure alteration will appear in a separate tab of the SQL Window. The template is similar to that used for a stored procedure creation.

Drop Stored Procedure

To drop an existing stored procedure, select it in the Object browser and use the menu item Others -> Stored Procedure -> Drop Stored Procedure. Then confirm the operation in the appeared dialog.