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.

1) IN
2) OUT
3) INOUT

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;

Output:
4

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.

Thanks
A Rahim khan

Advertisements
    • G Mallikarjun
    • November 23rd, 2011

    please can you post how to use these stored procedures in c#.net

    thanks in advance

    Mallikarjun

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

%d bloggers like this: