Using Stored Procedures in SQL (MySQL)

Stored Procedures seem in many respects quite similar to functions in SQL. However, they do have different use-cases.

Stored Procedure vs Function

A stored procedure can run INSERT, UPDATE and DELETE calls while a function cannot. This is because, a procedure does not have to return values as an output, whereas a function does – a function can only have SELECT statements.

Furthermore, a function can be used as part of your select statement (e.g. SELECT func(9) as cost); whereas a procedure cannot.

You may want to use a stored procedure to extract, update, delete and insert data in batch and use a function to run repeatable code to analyse that data – the result of the function can then be further utilized in your query.

Implementing Stored Procedure

Stored procedures asre simple to implement. Let’s walk through the below:

  1. Define the delimiter. This will wrap the query, you can see that we define $$ as the delimiter. This appears at the bottom of the function to tell SQL that the function is complete.
  2. We now create the procedure, in this case I have called it cust.
  3. We must define BEGIN and END tags to show the where the procedure content starts and ends.
  4. We can call the procedure using teh CALL function
DELIMITER $$
CREATE PROCEDURE cust()
BEGIN
    select * from myfavoritetable;
END$$
CALL cust()

Looking at a slightly more complex implementation. Here, we have added the ability to bring a parameter into the procedure. We pass the number 5 into the procedure & that can be used in the query.

DELIMITER $$
CREATE PROCEDURE cust(x int)
BEGIN
    select age * x from myfavoritetable;
END$$
CALL cust(5)

Here is an example of using multiple parameters in the procedure:

DELIMITER $$
CREATE PROCEDURE cust(x int, y int)
BEGIN
    select age * x from myfavoritetable where age > y;
END$$
CALL cust(5, 30)

Now let’s add some more logic; we’ll say – if Y >5 we should run statement 1; else we should run statement 2.

DELIMITER $$
CREATE PROCEDURE cust(x int, y int)
BEGIN
    IF y >5 THEN
        select 'Over5' as agegroup, age * x from myfavoritetable where age > y;
    ELSE
        select 'under5' as agegroup, age * x from myfavoritetable where age > y;
    END IF;
END$$
CALL cust(5, 30)

You can also use prepared statements to execute SQL. Below is a simple example where we simply pass SQL into the function and it gets run. If we couple this functionality with some of the other components above, you can see how this would become quite powerful.

DELIMITER $$
CREATE PROCEDURE runsql(IN s LONGTEXT)
BEGIN
    SET @sql = s;
    PREPARE stmt from @sql;
    EXECUTE stmt
    DEALLOCATE PREPARE stmt;
END $$
CALL runsql('select * from db.tbl')

Right, so that’s cool – we have some simple stored procedures defined. Now, let’s make it a little bit more interesting. We have already seen how we can define input parameters; but what about output parameters?

The below adds the concept of an output value. In this query:

  1. We declare a new variable called avgAge and set the default value to zero
  2. Then we run a query which calculates the average age across all users in a table. We update the avgAge variable to be the average value
  3. Now, we run a query to count the number of customers who’s age is lower than the average, using our new variable in the script.
DELIMITER $$
CREATE PROCEDURE avgcount(OUT belowx INT)
BEGIN
    DECLARE avgAge INT DEFAULT 0;
    SELECT AVG(age) INTO avgAge FROM myfavoritetable;
    SELECT COUNT(*) INTO belowx FROM myfavoritetable WHERE age < avgAge;
END $$
CALL avgcount(@belowx)
SELECT @belowx;

In future, I can call the stored proc by simply using the below:

CALL avgcount(@belowx);
select @belowx;

You can set static values too, using SET variablename = value; instead of DECLARE.

Dropping a stored procedure is simple: DROP PROCEDURE IF EXISTS ProcedureName

Share the Post:

Related Posts