Skip to main content

Creating Stored Procedures

In this tutorial, we will delve into the topic of creating stored procedures in MySQL. This tutorial is designed to be comprehensive, easy to understand and beginner-friendly. We will start with the basics of stored procedures and then gradually move on to creating and using stored procedures in MySQL.

What is a Stored Procedure?​

A stored procedure is a set of SQL statements that can be stored in the server. Once this has been done, clients don't need to keep reissuing the entire sequence of statements; instead, they can refer to the stored procedure. This provides a few advantages:

  • Efficiency: Stored procedures are parsed and optimized as soon as they're created.
  • Safety: Because stored procedures are usually written by DBAs and not by end-users, this provides a more controlled environment.
  • Functionality: Stored procedures can be written in a language that includes features not found in standard SQL.

Creating Stored Procedures​

To create a stored procedure, you use the CREATE PROCEDURE statement. Here is the basic syntax:

CREATE PROCEDURE procedure_name([parameter1 [type1], ...])
[characteristics ...] routine_body
  • procedure_name: The name of the procedure.
  • parameters: The parameters of the procedure, if any.
  • routine_body: The SQL statements to be included in the procedure.

Let's create a simple stored procedure without any parameters:

CREATE PROCEDURE `simpleproc`()
BEGIN
    SELECT 'Hello, World!';
END;

In the above example, simpleproc is a stored procedure that will simply output the string 'Hello, World!'.

Calling Stored Procedures​

To call a stored procedure, you use the CALL statement. Here is the basic syntax:

CALL procedure_name([parameter1, ...])

To call the simpleproc stored procedure we created earlier, you would use:

CALL simpleproc;

This will output 'Hello, World!'.

Stored Procedures with Parameters​

You can also create stored procedures with parameters. Let's create a stored procedure that takes two parameters and outputs their sum:

CREATE PROCEDURE `add_numbers`(IN num1 INT, IN num2 INT)
BEGIN
    SELECT num1 + num2;
END;

In this example, add_numbers is a stored procedure that takes two integers as input (specified by the IN keyword) and outputs their sum.

To call this stored procedure, you would use:

CALL add_numbers(5, 3);

This will output '8'.

In conclusion, stored procedures are a powerful feature in MySQL that allow you to encapsulate a sequence of SQL statements in a routine that can be called by clients. This can make your applications more efficient, safe, and functional. Start experimenting with stored procedures today to see how they can benefit your applications.