In this blog we are going to discuss about mysql stored procedures, its variables, loops, and Input – Output parameters.

What is stored procedure?

It is a subroutine like a subprogram or in a simple language a function that are stored in the database. It has a name, parameters list, and SQL statements that execute on demand.

Why we need stored procedures?

We need it to optimize the program execution time of backend in case of multiple database calls. For example, if your application makes multiple database request which is dependent in nature like you have users table in which company and incremental employee id are saved. Suppose you want to add a new employee then in this case you have to get the last employee id, and on that basis, you will increase employee id by one and save to database. And the Same for the new company, you will get the last company id, increase the value by one and same it.

In both cases, you will see there are two database calls, one after another. Now if you have some network latency then it will consume time. So, to optimize it, we can do this operation in one database call by putting our logic inside the stored procedure.

Basic structure of stored procedure

CREATE PROCEDURE `your_stored_procedure_name` ()
BEGIN

#Your logic here

END

Working with variables

Declare variable

To declare a variable inside a stored procedure, you can use DECLARE statement. These variables have their own scope that defines its lifetime.

DECLARE your_variable_name datatype(size) DEFAULT default_value;

For multiple variables

DECLARE var1, var2 datatype(size) DEFAULT default_value;

Assign value

SET your_variable_name = value;

Session Variables

Session variables are the variables whose name begins with the @ sign. It is available and accessible until the session ends.

SET @variable_name := value;

if-elseif-else in stored procedure

if exists (Your_statement_here) then
        #your_code
elseif exists (your_second_statement_here) then
        #your_second_code
else 
        #your_third_code
end if;

Check the value is null

IF(NAME_OF_THE_VAR IS NULL) THEN  
    #your_code
END IF;

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.