Reading Time: 15 minutes

8,816 Views

Inside this article we will see about Create And Call MySQL Stored Procedure with Code. This will be from step by step code background.

MySQL stored procedures are a method of encapsulating SQL statements into a reusable, modular package. They are created and saved in the database, where they can be called by applications or other procedures to perform complex tasks.

In an article, we have seen the complete basics of MySQL stored procedure. If you haven’t seen that, Click here to go. In that, we discussed about PhpMyAdmin Tool Interface to create a stored procedure. Also we had seen about details concept of parameters that is required to create mysql stored procedure.

Learn More –

Let’s get started.

How can we create a MySQL Stored Procedure ?

We can create stored procedure in MySQL in two different ways. First all about using MySQL Manual Tool & Second via MySQL create procedure command.

By Using Command
By Using PhpMyAdmin Manual Interface – you can find here.

Create Procedure

Previously given article is for creating stored procedure using Manual Tool. Now, inside this article we will see using MySQL Command.

By default MySQL Procedure is associated with the default database which we are using. But when we want to associate stored procedure with other given database, we need to specify the name as DB_name.StoredProcedure_name when we create it.

In case if we are already inside any database then mysql procedure will be created inside that. No need to add database name in syntax.

Here is the simplest syntax we are looking:

DELIMITER //

CREATE PROCEDURE Stored_Procedure_Name([sp_parameter[,...]])
BEGIN
	MYSQL CODE LOGICS   
END //
DELIMITER ;

sp_parameter: [IN | OUT | INOUT]

Explanation of Create Procedure Command

Here Inside code, first line is “DELIMITER //” , This line of syntax actually not related with stored procedure syntax. This is only used to change the default standard delimiter which is a semicolon (;). So here DELIMITER // means instead of default ; we are using //

Okay it’s fine, but Why we need to change the delimiter?
It’s because we would like to pass the stored procedure as a whole to the mysql server rather than mysql tool interpret each line at a time.

Following “END” keyword we use the DELIMITER // to indicate the end of mysql stored procedure. Last added line “DELIMITER;” in command changes the delimiter back to the default symbol i.e semicolon(;)

About sp_parameter we will discuss later when we will see about parametrized stored procedures. Parameters which we pass are of any type from IN, OUT & INOUT.

IN – Used as Input Parameters

OUT – Used as Output Parameters

INOUT – Used as Input & Output parameters

The section or code between the keywords BEGIN and END is called the body or logics of the stored procedure. We generally put the declarative SQL commands or statements in the body to handle business logic.

Inside above syntax we are using CREATE PROCEDURE statement command. We will understand about each part of this command by creating a stored procedure.

Let’s create our first mysql stored procedure.

My First Stored Procedure

We are creating a very very simple stored procedure. About the syntax we already discussed above. So we will use the same Create Procedure syntax to create.

Have a look inside this given image, We have selected a database namely cloud from our PhpMyAdmin database, you can choose your own. Then we clicked on SQL tab, Inside that command tab we did the mysql code to create stored procedure. Press Ctrl + Enter Or Go button you should see underneath of command panel.

It’s pretty simple.

Here is the available code you can copy. Inside this stored procedure we are simply selecting all the data from tbl_products table.

 DELIMITER //
  CREATE PROCEDURE sp_get_all_products()
    BEGIN
       SELECT *  FROM tbl_products;
    END //
  DELIMITER ;

After creating this stored procedure if we want to check in database. Have a look. A new section (node) will be created with the name as Procedures inside your selected database. If your database already have several stored procedures created, then the same node will get updated.

Step by Step Code Examination

DELIMITER // => This line is for changing default delimiter which is semicolon (;)
CREATE PROCEDURE sp_get_all_products() => CREATE PROCEDURE this is the keyword we need write while creating store procedures and sp_get_all_products() is the name of stored procedure.
BEGIN => SQL Code logics starts from this keyword. Now the body of stored procedure will start.
SELECT * FROM tbl_products; => SQL query to select all the data from specified table tbl_products
END // => This is the keyword which indicates that stored procedure body has been defined. Stored procedure has been end now.
DELIMITER ; => This delimiter keyword again now revert the used // to default standard delimiter (;)

How can we call MySQL Stored Procedures ?

As we have created MySQL stored procedure above. Now, need to call it. To Call stored procedures, we have a very basic command to call it.

Command Used : CALL stored_procedure_name();

Calling above created stored procedure – CALL sp_get_all_products();

This command will list all products data from table. Inside this we haven’t used any dynamic variables like to use in Conditional statements – where conditions. About using variables in MySQL stored procedure tutorial we will see now in seconds.

Let’s see mysql variables basics –

How can we Declare Basic Variables in MySQL ?

So far, the stored procedures we have created is very very simple means no logic, no dynamicity etc. But now inside this module we will see how can we create variables and used them into stores procedure body.

Inside the above given stored procedure we are simply selecting data from tbl_products table. Also we understood that it is deterministic in nature because of getting same result every time.

For getting dynamic data, means of any specific product we need to pass product id into query. So, for that we need a variable.

Let’s first understand about MySQL variables.

About MySQL variables

As I think you know about the basic definition of variables. If not, not an issue, we will see it here.

In simple terms Variables are the containers to store values. We can create variables of some pre defined data types. Data type here means which type of data we are going to store into that variable. Data type will of like String, Char, Integer, Decimal, Float etc.

Syntax :

DECLARE variable_name DATA_TYPE DEFAULT default_value;

Example :

DECLARE product_id INT DEFAULT 0;

Explanation of syntax

Inside above syntax DECLARE is the MySQL keyword used to declare variables. product_id is the name of variable. INT is the data type means product_id will store integer value. DEFAULT is the MySQL keyword used to store default value. If no value is there for product_id then 0 will be used.

Great ! we understood about mysql variables.

Using MySQL Variables in Stored Procedure

We are considering the same stored procedure what we have created above. In that same we will declare a variable which contains product id.

On the basis of product id we will add where condition into the query and find a specific product from data set.

DELIMITER //
  CREATE PROCEDURE sp_get_single_product_details()
    BEGIN
       DECLARE product_id INT DEFAULT 3; 
       SELECT *  FROM tbl_products WHERE id = product_id;
    END //
  DELIMITER ;

So here inside this query product_id will be equals to 3. Behind the scene select query will be something like this:

SELECT * FROM tbl_products WHERE id = 3;

Default value will be used when we don’t define the value of variable. Let’s define some user value instead of default.

We have to use SET keyword to set value of a variable. SET is the MySQL defined keyword to set value to a variable. Have a look

DELIMITER //
  CREATE PROCEDURE sp_get_single_product_details()
    BEGIN
       DECLARE product_id INT DEFAULT 3; 
       SET product_id = 10; 
       SELECT *  FROM tbl_products WHERE id = product_id;
    END //
  DELIMITER ;

Here we have defined the default value of product_id equals to 3 and also if you notice then also we set the value of product_id equals to 10. It means the default value 3 will not be used anymore.

Generated Query

SELECT * FROM tbl_products WHERE id = 10;

Running Procedure with variables at PhpMyAdmin

Back to PhpMyAdmin > Open Database > SQL Tab > Write Query to Create > Ctrl + Enter

Back to the LIST of all created of Stored Procedures. We should see the created stored procedure into the list. Simply if we want to run, we will use the same syntax of Call Statement what we have discussed previously.

Directly go to SQL tab again and run the command:

CALL sp_get_single_product_details()

This time it will pick a specific record from table. We are getting a specific product of ID = 10.

Article QA – Interview Questions

Inside this article we have few questions which you need to keep in your mind. These question helps you for the interview when someone asks.

How many types we have to create MySQL stored procedure ?
Can you write the syntax to create a stored procedure ?
What CREATE PROCEDURE command does ?
Please write the syntax of create procedure and explains about each line of syntax.
What is the importance of DELIMITER keyword ?
Can you explain the block keywords – BEGIN and END.
Please write a program to create a stored procedure in MySQL.
How can we call a stored procedure in MySQL ?

We hope this article helped you to learn Create & Call MySQL Stored Procedure in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.

Create and Call MySQL Stored Procedure