Blog

What is a Store Procedure?

In simple words store procedure is a procedure of storing mysql queries for further use.

Let’s discuss in detail, a store procedure is a prepared sql statements or code that we save in our database for reuse same code over and over again whenever we need.

Following query returns all records from the users table

select * from users

Above picture shows the output of select query

So, if you want to use the above query over and over again, save it as store procedure and then just call to execute it

Why store procedure?

Sql stored procedures bring with them some stored efficiencies.  When the logic being stored on the server we can avoid some network traffic.  So as opposed to having to send multiple commands from a client to a database we can send one command to say execute a stored procedure which then will execute our multiple commands.  In addition, stored procedures are cached on the database server so once they are run, they are ready to run again.

Syntax of store procedure :

CREATE PROCEDURE procedure_name(parameter_list)
BEGIN
   statements;
END $$

1.After CREATE PROCEDURE keyword procedure name must be given that you want to create.

2. Second, specify a list of comma-separated parameters for the stored procedure in parentheses after the procedure name.

3.Write statements in between BEGIN and END block. After the END keyword, we place the delimiter character to end the procedure statement.

How to create a store procedure?

The following statement creates a new stored procedure

DELIMITER $$
CREATE PROCEDURE GetAllUsers()
BEGIN
    SELECT *  FROM users;
END $$
DELIMITER ;

To execute above statements first open phpmyadmin console

Paste above statements into phpmyadmin console and execute it by pressing Ctrl+Enter.

 

After execution of above statements check the store procedure by opening Procedures node. If you are not able to see refresh the page.

How to Execute store procedure :

To execute a stored procedure, use CALL statement :

CALL stored_procedure_name(argument_list);

1.After the CALL keyword we have to mention store procedure name.

2.If store procedure have parameters then we have to pass parameters inside parentheses.

Take a look on below example

CALL GetAllUsers();

To execute store procedure, first open phpmyadmin console and paste above statement and press Ctrl+Enter. This will give all records.

After pressing Ctrl+Enter you will get output like shown in below image

Mysql Delimiter :

When we write multiple sql queries we use the semicolon(;) to separate two statements

for example,

select * from users;
select * form countries;

If we use a Mysql client program to define a stored procedure that contains semicolon, the Mysql client program will not treat the whole stored procedure as a single statement.

Therefore we must redefine temporarily delimiter so that we can pass whole store procedure as a single statement to the server.

Delimiter character can be single character or bunch of characters ex. $$ or // , except ( \ ) because this is the escape character in Mysql.

To defined temporarily delimiter we have to use DELIMITER keyword followed by delimiter character.

For example,

DELIMITER $$

To change the delimiter back to semicolon, we have to use following statement:

DELIMITER ;

How to use delimiter in store procedure :

DELIMITER $$
CREATE PROCEDURE store_procedure_name()
BEGIN
  -- statements--
END $$
DELIMITER ;

In above statements,

1.change default delimiter to $$.

2.we have to write “;” after the statements.

3.After END keyword use $$.

4.Change default delimiter back to semicolon(;).

How to delete store procedure :

To delete store procedure from the database we have to use DROP PROCEDURE keyword .

Syntax for DROP PROCEDURE keyword :

DROP PROCEDURE [IF EXISTS] stored_procedure_name;

To delete store procedure we have to write DROP PROCEDURE followed by store procedure name

For example

DROP PROCEDURE IF EXISTS GetAllUsers;

To delete store procedure first open phpmyadmin console and paste above statement with your store procedure name.

After pasting above command and pressing Ctrl+Enter will ask for confirmation for deleting store procedure or not, clicking on OK will delete store procedure.