Dev

By Carlos Santana on
Reading time: 5 minutes

MySQL-Blpsp.png

So far I have written about React in this blog, but I would like to start writing about other important topics and technologies. In this post, I'll talk about how to create Stored Procedures with MySQL. Yesterday I was pulling my hair out trying to create some Stored Procedures (SP from here) in MySQL but I learned some tricks which I hope can be useful for you.

Before you write any line of code, I widely recommend you to install MySQL Workbench you will thank me later.

The first command I'll explain you is DROP PROCEDURE IF EXISTS, this command is needed to delete the SP if already exists (this needs to be added before the DELIMETER), this is very helpful when you're updating constantly an SP, the syntax is this: 

DROP PROCEDURE IF EXISTS myProcedure;

The next command is the DELIMITER, is needed to write the SQL queries, you can choose any delimiter but the most common are:

DELIMITER //
  ...
//

Or:

DELIMITER $
  ...
$

After you write the delimiter, let's create the SP with the command CREATE PROCEDURE, like this:

CREATE PROCEDURE myProcedure({[PARAMS]})
BEGIN
  ...
END

This looks like old Pascal code where we specify the BEGIN and END blocks, the parameters are optional.

Parameters

When we create a new SP the params are optional and are very helpful when we need some values, you can specify the params like this:

CREATE PROCEDURE myProcedure(
  IN _language VARCHAR(2),
  IN _page INT, 
  IN _max INT)
BEGIN
  ...
END

When you need to add more than 3 or 4 params is a good idea to add break lines. The syntax for these parameters is just to add the IN command before the name of the parameter (I recommend you to add an underscore as a suffix on the variables, I'll explain to you why in the next paragraph), finally, you specify the type (INT, VARCHAR, TINYINT, etc.).

Declaring variables

When we create an SP in MySQL we can declare variables to assign some values or to do some validations. In order to declare a variable, you have to use the DECLARE command, as I mentioned before I recommend you to add an underscore as a suffix on the variables and also to use the camelCase technique, this is because sometimes we can create variables with generic names that could be reserved words of MySQL and this can cause problems without knowing at the beginning and is really hard to find that kind of issues, for example, if we declare the variable "status", it can cause a problem because STATUS is a MySQL command that is used with: SHOW PROCEDURE STATUS, so in this case is better to declare the variable as "_status" and we won't have this kind of problems, let's declare some variables:

DECLARE _start INT DEFAULT 0;
DECLARE _limit1 INT DEFAULT 0;
DECLARE _limit2 INT DEFAULT 0;
DECLARE _language VARCHAR(2) DEFAULT 'en';

Once a variable is declared and you need to change the value at some point of the SP you can use the command SET like this:

IF _page > 0 THEN
  SET _limit1 = _page * _max - _max;
END IF;

IFs

Like any language sometimes we need to bifurcate our codes and take different paths depending on the situation, in MySQL we have the possibility of using the IFs when creating our SPs, although they are undoubtedly used a bit "rudimentary" and reminds me of Pascal without a doubt, but here I leave some IFs blocks that you can use. 

IF _variable1 > 0 AND _variable2 == 1 THEN
  ...
END IF;

IF _variable1 > 0 OR _variable2 == 1 THEN
  ...
ELSE
  ...
END IF;

IF _variable1 == 0 OR _variable2 == 1 THEN
  ...
ELSE
  IF _variable1 <> 5 THEN
    ...
  END IF;
END IF;

Selects

The MySQL SELECT is normally used to "select" the fields and do some conditions to get some rows of a specific table. Even if in an SP is useful to do the same, they have another use which is to return the selected values as results (that means is similar to use a return inside a function, but an interesting thing is that in an SP you can't use return).

An example of this is when we want to do a debug function and use it in our SP. Let's create our debug SP: 

DELIMITER $
CREATE PROCEDURE debug(msg VARCHAR(255))
  BEGIN
    SELECT CONCAT("*** ", msg) AS '*** DEBUG:'; 
  END $
DELIMITER

In the debug SP, we receive msg as a parameter which can be any value or variable we can to "debug". The CONCAT function is to concatenate one string with another, in this case, we are concatenating the "***" string with the msg variable, this is to identify quickly the debug message we are getting, to use it you can call it from other SP like this:

DROP PROCEDURE IF EXISTS getPosts;

DELIMITER $

CREATE PROCEDURE getPosts(
    IN _language VARCHAR(2),
    IN _page INT, 
    IN _max INT)
BEGIN
    DECLARE _start INT DEFAULT 0;
    DECLARE _limit1 INT DEFAULT 0;
    DECLARE _limit2 INT DEFAULT 0;

    SET _limit1 = 0;
    SET _limit2 = _max;

    CALL debug(_limit2);
END $
DELIMITER ;

As you can see we can know the value of the "_limit2" variable, and we can see that in the resultset when we execute the SP.

We can have many SELECTs in our SP, and we will be able to see each SELECT we use to return a value in the result set, for example, when we use one SELECT we will get something like this: 

[
  [0]: {
    "Field": "value"
  }
]

Now if we use two SELECTs (one with our debug) then our result set will be:

[
  [0]: {
    "**Debug:": "The debug message"
  },
  [1]: {
    "Field": "value"
  }
]

As the last advice I can tell you that the LIMIT command only accepts INT values divided into two variables, that's why I defined _limit1 and _limit2, at the beginning I tried to do it concatenating the numbers as strings like "0, 10", but that never worked, so I think this is useful information for you.

This is the SP I created yesterday which is useful to get the posts of a blog table:

DROP PROCEDURE IF EXISTS getPostsByCategory;

DELIMITER $

CREATE PROCEDURE getPostsByCategory(
    IN _category VARCHAR(100),
    IN _language VARCHAR(2),
    IN _page INT, 
    IN _max INT)
BEGIN
    DECLARE _start INT DEFAULT 0;
    DECLARE _limit1 INT DEFAULT 0;
    DECLARE _limit2 INT DEFAULT 0;

    SET _limit1 = 0;
    SET _limit2 = _max;

    IF _page > 0 THEN
      SET _limit1 = _page * _max - _max;
    END IF;

    SELECT COUNT(1) AS total 
      FROM (
        SELECT blog_posts.id FROM blog_posts 
          LEFT JOIN blog_re_categories2posts ON (blog_re_categories2posts.postId = blog_posts.id) 
          LEFT JOIN blog_categories ON (blog_categories.id = blog_re_categories2posts.categoryId) 
          WHERE blog_categories.slug = _category 
          GROUP BY blog_posts.id
      ) AS Result;

    SELECT  
      title, 
      blog_posts.slug, 
      excerpt, 
      content, 
      author, 
      mainImage, 
      createdAt, 
      day, 
      month, 
      year, 
      blog_posts.language, 
      GROUP_CONCAT(blog_categories.category SEPARATOR ', ') AS categories
      FROM blog_posts 
        LEFT JOIN blog_re_categories2posts ON (blog_re_categories2posts.postId = blog_posts.id)
        LEFT JOIN blog_categories ON (blog_categories.id = blog_re_categories2posts.categoryId)
        WHERE blog_categories.slug = _category  
          AND blog_posts.language = _language 
          AND blog_categories.language = _language
          AND blog_posts.situation = 'Published'
      GROUP BY blog_posts.id
      ORDER BY blog_posts.id DESC
      LIMIT _limit1, _limit2;
END $
DELIMITER ;

Remember the way to call an SP is by using the CALL command in our query:

Only members can see all the codes
You can Login or Sign Up

avatarLeave a comment

Your comment

Only members can comment. You can Login or Sign Up