SQL Routines to make reusable code

SQL routines include procedures and functions using which we can define custom logic to meet our business needs that are modular, easy to maintain, and reuse. SQL provides a number of inbuilt functions such as SUM, AVG, CONCAT, etc. that performs predefined task and returns processed result. While these functions are powerful, they may not serve all our needs or might involve a lot of repetition to achieve our needs. Using SQL routines we can define similar callable functions that serve specific needs as per the requirement.

Stored procedure and function are both a block of code that is reusable but differ in a number of ways. A stored procedure is a logically grouped SQL statement that is saved into to database and can be reused over and over again across different programs. User-defined functions are functions created by users that perform data processing as per business requirements. Other differences are that stored procedures are capable of executing SQL statements whereas functions can’t and functions can be called within SQL statements like other inbuilt functions but we can’t do the same with stored procedures. In this article, we will look into an example of how we can use SQL routines to create reusable blocks of code.

Let’s consider a simple database that saves data related to a product and its order involving 4 tables with fields as follows

1. category
   id // primary key
   category_name // category’s name
2. product
   id // primary key
   product_name // product’s name
   cost // product’s cost
3. product_category
   id // primary key
   product_id // product’s id
   category_id // category’s id
4. product_order
   id // primary key
   product_id // ordered product’s id

Considering the above structure if we were to calculate total sales for each category then we would do it by maintaining the relation between the category and sold product’s cost by joining tables as shown below

SELECT category.category_name as Name, SUM(product.cost) as Total
FROM
Category
LEFT JOIN product_category ON product_category.category_id = category.id
LEFT JOIN product ON product.id = product_category.product_id
JOIN product_order ON product_order.product_id = product.id
GROUP BY category.id

This gives output similar to below

Not much of an issue for single cases but if we need to show the category’s total sales for other scenarios as well then we need to repeat the same joins in those statements which increases the complexity of code maintenance and debugging. If we can make this block of statement behave like any other in built MYSQL functions then we could improve efficiency. For the same purpose, we will be creating a procedure and function that returns the category’s total sales by passing the category’s id.

Creating Procedure

Since we are working with the SQL statement, it should be created as a stored procedure as functions aren’t capable of doing this. The procedure takes input parameters, returns defined parameters, and includes SQL statements as a definition. For our example, we will create a procedure that takes the category’s id, and SQL statement that calculates the total sales amount for that category and returns the calculated total amount.

CREATE PROCEDURE ExecCategoryTotalSales(category_id int, OUT total float)
BEGIN
SELECT SUM(product.cost) INTO total FROM category
JOIN product_category ON product_category.category_id = category.id
JOIN product ON product.id = product_category.product_id
JOIN product_order ON product_order.product_id = product.id
WHERE category.id = category_id;
END

Creating Function

Now that we have created the procedure we need to make it callable from SQL statement so that we can use it as per our requirement. Unfortunately, procedures can’t be called from SQL statements but they can be from user-defined functions. So we will create a new function that calls the procedure created above. Similar to procedure, function takes in parameters and returns required data but the definition can’t involve SQL statements, instead we can define other business logic to achieve the need. We can include loops, and conditions inside the user-defined function similar to another programming language so that we can manipulate data to our needs. In this example, we will accept category’s id as input parameter, simply call the procedure as its definition, and return the data returned by procedure.

CREATE FUNCTION GetCategoryTotalSales(category_id INT) 
RETURNS FLOAT
BEGIN 
DECLARE total FLOAT DEFAULT 0; 
CALL ExecCategoryTotalSales(category_id, total); 
return total; 
END

Calling from statement

Now that we have the procedure and function created, we can call it from a statement like any other in-built function. The output that we got from running the initial query to get sales by category can be calculated with the following query using a reusable function.

SELECT category.category_name as Name, GetCategoryTotalSales(category.id) as Total from category;

With this, we will have a single source of information for category sales making it reliable and consistent wherever it is used. In this way, we can decompose large blocks of complex codes into small segments making them reusable.

Thank you