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:
- 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.
- We now create the procedure, in this case I have called it cust.
- We must define BEGIN and END tags to show the where the procedure content starts and ends.
- 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)$$
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:
- We declare a new variable called avgAge and set the default value to zero
- 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
- 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