Question

MySQL Conditional stored procedures IF - CASE

Answer 1

Procedures stored with conditional IF

We will create a database and a table with comments that users make, they could be articles, posts or whatever we want.

CREATE TABLE `comments` (
`idcommentary` int (11) DEFAULT NULL,
`date` date DEFAULT NULL,
`userid` int (50) DEFAULT NULL,
`comment` text,
PRIMARY KEY (`idincidence`)
) ENGINE = MyISAM DEFAULT CHARSET = latin1;


We are going to create a stored procedure that counts the amount of comments that a user has left and returns us the level in which that user is, taking as reference for the example.

Expert if you have made more than 5000 comments
Advanced if you have commented between 4999 and 1000 times
Moderator if you have commented between 999 and 500 times
Editor if you have commented between 499 and 100 times

. We create the stored procedure and it will have two parameters, one will be input the id or user number whose variable is p_user and the other one that will be the level with the variable p_level .

We explain the stored procedure we call it, we assume an id = 112
CALL nivelusuario (112)
Select and count the number of comments for this user and store it in the total variable, then the structure if you assign the level to the variable p_level and return the value.

<? // We prepare a query from php
$ result = $ mysqli-> query ("CALL nivelusuario (112)");
$ row = $ result-> fetch_assoc ();
echo 'Level:'. $ row ['p_level']; ?>

Procedures stored with conditional CASE

The operation is very similar to the previous one, only the conditional is CASE and in this example we obtain the cost of sending or surcharge according to the location of a client.

We will assume an idcity = 2 and call the stored procedure
CALL costenvio (2)

<? // We prepare a query from php
$ result = $ mysqli-> query ("CALL costenvio (2)");
$ row = $ result-> fetch_assoc ();
echo 'Surcharge for delivery'. $ row ['p_recargo']; ?>

Also within the possibilities provided by the conditional structures, we can use the if or case structures to perform several actions with a single stored procedure
We will create for example a stored procedure to manage products to which sending a parameter we can then determine what action or task we will perform.

We can see that in the product process we have a parameter called p_accion, so we assign if the action will save a new product or modify in the database

Example to insert a new product

<? // We prepare a query from php
$ result = $ mysqli-> query ("CALL products (10, 'Mobile phone', '100.00', 'new')");
echo 'The product has been registered'; ?>

Example to insert an edit or modify a product

<? // We prepare a query from php
$ result = $ mysqli-> query ("CALL products (10, 'MXC3 mobile phone', '120.00', 'edit')");
echo 'The product has been modified'; ?>

This method is very useful for grouping tasks known as CRUD ie create, read, edit and delete data and perform updates and searches all ordered in the same file, thus facilitating maintenance and the ability to scale the database if necessary. .
It also allows from the point of view of the functional analysis to have more clear the business rules of the application.

---