MySQL Stored Procedure

A stored procedure is a set of SQL statements that are pre-compiled and stored in the database server. Stored procedures allow you to encapsulate complex business logic and database operations into a single, reusable code block that can be called from multiple applications or scripts. MySQL supports the creation of stored procedures using the following syntax:

CREATE PROCEDURE procedure_name (parameters)
BEGIN
    -- SQL statements here
END;

Let’s take a closer look at this syntax:

CREATE PROCEDURE is the MySQL command used to create a new stored procedure.
procedure_name is the name you choose for your stored procedure.
parameters are optional input parameters that can be passed to the stored procedure when it is called.
BEGIN and END define the beginning and end of the stored procedure’s code block.
SQL statements are the actual SQL statements that make up the stored procedure’s code.

Here is an example of a simple stored procedure that takes two input parameters and returns their sum:

CREATE PROCEDURE add_numbers (IN num1 INT, IN num2 INT, OUT result INT)
BEGIN
    SET result = num1 + num2;
END;

In this example, the add_numbers procedure takes two input parameters (num1 and num2) and one output parameter (result). The SET statement calculates the sum of num1 and num2 and stores the result in the result parameter.

To call this stored procedure, you can use the following syntax:

CALL add_numbers(5, 10, @sum);
SELECT @sum;

This will call the add_numbers stored procedure with the values 5 and 10 for num1 and num2, respectively, and store the result in the @sum variable. The second statement then selects and displays the value of @sum.

You can also use conditional statements, loops, and other programming constructs within stored procedures to create more complex logic.

Share on Facebook

Save