Stored Procedure in MySQL Server
Stored Procedure is a collection of SQL commands and this is not executed until or unless called but compiled at the time of creation. Thus, this helps to reduce execution time and increases Data Integrity as well. In MySQL Server, Stored Procedure accepts three types of argument.
Let’s start with a simple example.
CREATE PROCEDURE square_number(INOUT number_one INT) BEGIN number_one = number_one * number_one; END
square_number takes a number as argument and returns square of the number. Stored Procedure is called in MySQL Server in the following way.
SET @a=2; CALL square_number(@a); SELECT @a;
However, if you are using MySQL Command Line Utility, you have to change the DELIMITER value. The default MySQL Statement delimiter is ; and this is same for MySQL Command Line Utility. In this case, write the Stored Procedure like this one.
DELIMITER// CREATE PROCEDURE square_number(INOUT number_one INT) BEGIN number_one = number_one * number_one; END// DELIMITER;
The same is true for PHPMyAdmin. You can do these kinds of MySQL stuffs easily using any free MySQL GUI. Have a look at one of my posting here on a free MySQL GUI.
Finally, I used very simple example here to explain Stored Procedure in MySQL Server, but this can reduce a lot of your headche if used efficiently.
A Rahim khan