5 things to master MySQL Stored Procedures

MySQL stored procedures or popularly known as SP is a widely used feature in MySQL. It will help you write and manage a large or small query set where you can add your business logic or run a specific task based on the requirement. Even though if I say it’s easy to learn if you understand these 5 things, for beginners it will seem to be a bit alien. But if you have a basic understanding of MySQL queries, you will be wondering to know that you can do these things inside MySQL.

The stored procedure is SQL statements wrapped within the CREATE PROCEDURE statement. The stored procedure may contain a conditional statement like IF or CASE or the Loops. The stored procedure can also execute another stored procedure or a function that modularizes the code.

What is stored procedure

Benefits

  • Speed – As multiple statements can be wrapped inside a single stored procedure, it will reduce multiple network call which will result in increase of speed of your application
  • Secure – Store procedures reside inside MySQL server and user permissions can be granted to execute procedures based on the table permissions. Also it helps from preventing SQL injections
  • Maintainability – Business logic can be implemented in store procedures and by keeping a SQL file of stored procedure you can do instant deployments by executing SQL file in MySQL server. Different modules can use same procedure which makes it highly reusable.

Simple stored procedure example

Below stored procedures give two results set for total users and total signups today

DELIMITER $$

CREATE PROCEDURE `sp_getUserStats` ()
BEGIN
     select count(*) as total_user from users;
     select count(*) as signups_today  from users where date(created_at) = CURDATE();
END$$
-- Call Statement
call sp_getUserStats();
-- Drop/Delete procedure
DROP procedure IF EXISTS `sp_getUserStats`;

Before you start reading, the examples shown are only meant to explain the concept and there are better ways to achieve those tasks.

1. Inputs and Variables

a. Variables

In MySQL, you can add a variable inside a stored procedure in two ways.

  • Declared variable (Eg:- DECLARE totalCount int default 0; )
  • Dynamic variable using @ (Eg:- SET @todaysTotal = 0; )
DELIMITER $$

CREATE PROCEDURE `sp_getUserStats` (in minAge int(11))
BEGIN
    DECLARE totalCount int default 0;
    SET @todaysCount = 0;
	select count(*) into totalCount from users;
	select count(*) into @todaysCount from users where date(created_at) = CURDATE();
    
    SET @convertionRate = totalCount / @todaysCount;
    select round(@convertionRate,2) as percentage;
END$$

call sp_getUserStats(18);
-- Output
-- 50

b. Inputs

Inputs to stored procedures are almost similar to function parameters. You can pass parameters to SP inside the parenthesis. 

(IN | OUT | INOUT) (Parameter Name [datatype(length)])

Check out this documentation to see the difference between IN, OUT, and INOUT types

Input params have three parts

  1. Type of parameter (IN, OUT, INOUT)
  2. Parameter name
  3. Datatype with length
DELIMITER $$

CREATE PROCEDURE `sp_getUserStats` (in minAge int(11))
BEGIN
	select * from users where age > minAge;
END$$
-- Call Statement to get users with age greater than 18
call sp_getUserStats(18);
DELIMITER $$

CREATE PROCEDURE `sp_getUserStats` (out total int)
BEGIN
	select count(*) into  total from users;
END$$
-- Call Statement to get users with age greater than 18
call sp_getUserStats(@total);
select @total as total;

Change above stored procedure to INOUT to test INOUT and run the query below to see the difference;

set @total = 14;
select @total;
call sp_getUserStats(@total);
select @total as total;

2. Conditional statements

There are mainly two conditional statements in MySQL. Both of them are pretty same compared to structure and workflow. It’s more of a personal choice to choose between IF and CASE

1 IF STATEMENT

   IF expression THEN commands
        [ELSEIF expression THEN commands ....]
        [ELSE commands]
   END IF;

 2. CASE STATEMENT
   CASE expression
        WHEN value THEN
            statements
        [WHEN value THEN
            statements ...]
        [ELSE 
            statements]
    END CASE;
DELIMITER $$

CREATE PROCEDURE `sp_getUserStats`()
BEGIN

    DECLARE total, today, average int default 0;
    select count(*) into  total from users;
    select count(*) into today from users where date(created_at) = CURDATE();
    set average = round(total/today);
    set @statusIf = 'Low';
    set @statusCase = 'Low';

    -- If statement
    IF average < 10 and average > 5  THEN
            set @statusIf = 'AVERAGE';
      ELSEIF average > 10 and average < 20 THEN
	    set @statusIf = 'Above Average';
      ELSE 
	    set @statusIf = 'High';
    END IF;
    
    -- Case statement
    CASE average
        WHEN average < 10 and average > 5  THEN
            set @statusCase = 'AVERAGE';
        WHEN average > 10 and average < 20 THEN
	    set @statusCase = 'Above Average';
        ELSE 
	    set @statusCase = 'High';
    END CASE;
    select @statusCase as `Grouth in users using CASE`, @statusIf as `Grouth in users using IF`;
END$$

DELIMITER ;

-- Call Statement
call sp_getUserStats()

Take look at this article to read more about the difference between IF and CASE

3. Loops

There are mainly three types of loop in MySQL Stored Procedures.

1. While loop

The example below selects users from id 1 to 5 and gives you 5 result sets. Initially, x variable value will have value as 1 and while loops run until x <= 5 conditions satisfy.

While loop Syntax
WHILE <CONDITION> DO
 ..
 ..
END WHILE;
DELIMITER $$

CREATE PROCEDURE `sp_whileLoop` ()
BEGIN
      declare x INT default 1;
      WHILE x <= 5 DO
	select * from users where id = x;
	SET x = x + 1;
      END WHILE;

END$$

-- sample call
call sp_whileLoop();

2. REPEAT loop

The same example mentioned in while loop can be done using repeat also, but instead of simply selecting all values in the users table this time let’s get all the emails ids as a string. Also take a look at the CONCAT function in MySQL if you are not familiar, which has a lot of applications in our next point.

REPEAT SYNTAX
REPEAT
 ..
 ..
 UNTIL <EXIT CONDITION>
END REPEAT;
DELIMITER $$

CREATE procedure sp_forLoop()
BEGIN
  DECLARE x INT;
  SET x = 1;

  loop_label: LOOP
    IF x > 5 THEN
      LEAVE loop_label;
    END IF;
    SET x = x + 1;
    select * from users where id = x;
    ITERATE loop_label;
  END LOOP;


END$$

DELIMITER ;

-- Sample call
call sp_forLoop();

3. FOR loop

FOR loop is one of the most common loops used in SPs. When combined with the cursor in MySQL FOR loop has the biggest application of looping through the select result set.

For Loop Syntax
LOOP_LABEL:LOOP
   IF <EXIT-CONDITION> THEN
      LEAVE LOOP_LABEL;
   END IF;
   ..
   ..
   ITERATE LOOP_LABEL;
END LOOP;
DELIMITER $$

CREATE procedure sp_forLoop()
BEGIN
  DECLARE x INT;
  SET x = 1;

  loop_label: LOOP
    IF x > 5 THEN
      LEAVE loop_label;
    END IF;
    SET x = x + 1;
    select * from users where id = x;
    ITERATE loop_label;
  END LOOP;


END$$

DELIMITER ;

-- sample call statement
call sp_forLoop();

3.1 Cursor FOR loop syntax example in MySQL:

MySQL FOR loop can be also used to loop select statement results using CURSORS. Probably this will be one most widely used loops. Take a look at MySQL Cursors to get more info.

CURSOR has two parts CURSOR and HANDLER. You can keep all data inside CURSOR and the Handler will check for exit conditions. You can fetch cursor values to a different variables for further actions.

The below example selects the first 5 users, then combine and prints their emails ids

DELIMITER $$

CREATE procedure sp_cursorLoop()
BEGIN
        DECLARE done INTEGER DEFAULT 0;
	-- declare cursor for user email
	DEClARE curEmail CURSOR FOR SELECT email FROM users limit 5;

	-- declare NOT FOUND/Stop  handler
	DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET done = 1;

	OPEN curEmail;

	userEmail: LOOP
		FETCH curEmail INTO emailAddress;
		IF done = 1 THEN 
		   LEAVE getEmail;
		END IF;
		-- build email list
		SET emailList = CONCAT(emailAddress,";",emailList);
	END LOOP getEmail;
	CLOSE curEmail;
        select emailList;
END$$

DELIMITER;

4. Dynamic query

The query can be executed from a string using MySQL Prepare and Execute functions. This opens a bigger window in MySQL to create a dynamic query using Loops and Conditions Statements combining strings using the CONCAT functionality. When comes to creating a dynamic query proper understanding of Concat function is a must.

PREPARE, EXECUTE, and DEALLOCATE Syntax

PREPARE variableName FROM '[select * from table]';
EXECUTE variableName ;
DEALLOCATE PREPARE variableName ;

Take a look at this article to get more ideas about Prepare Statement in MySQL. The below example will select given ids from any given table. You can pass table name as a string and ids as comma-separated string. The CONCAT statement will generate a select query and which is later called using prepare statement. 

USE `db_name`;
DROP procedure IF EXISTS `sp_dynamicQyery`;

DELIMITER $$

CREATE PROCEDURE `sp_dynamicQyery`(in table_name varchar(255), in ids varchar(255))
BEGIN
	 SET @getDataFromAnyTable = CONCAT("SELECT * FROM ", table_name, " where id in (", ids, ")");
         -- select @getDataFromAnyTable;
	 PREPARE stmt FROM @getDataFromAnyTable;
	 EXECUTE stmt;
	 DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

-- call statement
call sp_dynamicQyery('users','1,2,3,4,5');

Debugging dynamic queries is one of the most painful things you will be facing if came across an error while executing the query. The best way is to output the string query from the string variable where you feel the error might be, this will give an idea of whether there is any problem with the concat statement. You can also try executing the concat variable result separately and fix the error and later you can adjust the changes in concat after fixing the issue.

Example Scenario – This something happened to me while preparing this article

call sp_dynamicQyery('users','1,2,3,4,5'); was giving me an error.

SET @getDataFromAnyTable = CONCAT("SELECT * FROM ", table_name, "where id in (", ids, ")"); This was the code for concat variable. I tried selecting the @variable.

select @getDataFromAnyTable; will give result as select * from userswhere id in (1,2,3,4,5);

From above output, I came to know I missed a space before where condition.

5. Temporary table

A temporary table is what you are looking for if you have to store some intermediate data and do further actions or later update the data or both. While working with a huge database or for some particular requirements like data migration, you may have to write a long SP where you will come across creating a temporary table to store some intermediate data. Take a look at this article to get more details on a temporary table. The syntax is no different than creating a regular table and you can do all most all things similar to a normal table. The main difference is the temporary tables will get dropped when the session expires.

Below example create a new temp table for top purchases and values are inserted to top_purchases table and selected using the select query. After selecting the data table is dropped. 

CREATE PROCEDURE sp_tempTable(in departmentIds varchar(55))
BEGIN

 DROP TEMPORARY TABLE IF EXISTS top_purchases;

 CREATE TEMPORARY TABLE top_purchases(
    customer_name INT PRIMARY KEY,
    amount DEC(10,2)
 );

 INSERT INTO top_purchases SELECT u.name, sum(p.amount) as amount from users as u 
inner join purchases as p on u.id = p.user_id
group by u.id order by sum(amount) desc limit 5;

  SELECT * FROM top_purchases;

  DROP TEMPORARY TABLE top_purchases;

END$$

DELIMITER;

Leave a comment

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