SQL: Stored Routines

In previous posts, I have described where certain options could be taken to help increase the efficiency of making queries in SQL. In this post, I will go over one the most helpful tools to increase efficiency: Stored Routines.

What are Stored Routines?

There are two types of stored routines: procedures and functions. Procedures are, as described in stored routines, saved pieces of SQL code that can be called over and over again. Functions are user defined functions that are also saved to a specific database server. They are different from the built-in functions that are already programmed into SQL like the aggregate function.

These two might sound the same, however there are slight differences within the syntax for each that can make one type of routine better to use than another. Procedures allow for a wide range of coding and are easier to write, while functions are more rigid, but the returned variables are easier to manipulate than the output from stored procedures.

Procedure Syntax

DELIMITER $$CREATE PROCEDURE procedure_name (possible parameters)
BEGIN
SQL Code
END$$
DELIMITER ;

Let’s go through each step in the syntax.

The first step you see is a DELIMITER statement. The DELIMITER statement changes which symbol is used to end a SQL statement. However, when writing a procedure, you might be writing multiple lines of SQL statements. By changing the symbol, it allows the procedure to complete each statement within. Without it, there would be an error trying to create the procedure before using the END statement. Most commonly used symbols are $$ and // .

The next step is the CREATE PROCEDURE statement. This starts the process of creating the procedure. There is a procedure_name , which is self-explanatory. Then there are a set of parentheses. These are required even if there are no parameters. We will go over the procedure parameters later.

The main body of the syntax will involve the SQL Code . To start writing the SQL code that you wish to save, the BEGIN statement is used. Then you write the SQL code you want to save to the procedure and use the END statement to declare that you have finished writing the code. The $$ ends the SQL statement for creating the procedure and the final DELIMITER statement changes the symbol back from $$ to ; . So, when the procedure is then called, it can complete each piece of SQL coding.

Procedure Parameters

Once the type of variable is specified, you name a variable for the value being entered or passed out. Then the final step for declaring a parameter is to specify the data type the procedure should expect the value to be.

Procedure Example

DELIMITER $$CREATE PROCEDURE number_clients_state (IN state_v VARCHAR(20))
BEGIN
SELECT COUNT(*)
FROM clients
WHERE state = state_v;
END$$
DELIMITER ;

As mentioned in the syntax section, we change the delimiter to $$ . We create a procedure with a parameter for the entered State value which is a VARCHAR data type. The next step is to create the query by selecting the total number of clients and using the WHERE clause to filter the clients to those living only in the specified state. Once the procedure is created, the delimiter is changed back to the normal ; .

To call on this procedure, we use the CALL statement. So if we wanted to know the total number of clients from New York, who have used the online store, we can write the following.

CALL number_clients_state('New York');

This would return the number of clients in New York. As we can see, this would be much faster than having to write out the entire query every time we needed the information.

User Defined Functions Syntax

DELIMITER $$CREATE FUNCTION function_name (parameter_name data_type) RETURN data_type
(NOT) DETERMINISTIC
BEGIN
DECLARE variable_name data_type
SQL CODE
RETURN variable_name
END$$
DELIMITER ;

We use the delimiter due to the same reasoning as procedures. Then the CREATE statement is used with what you want to create, in this case a FUNCTION . Following these statements is the name of the function. It is important that you don’t name your function with the name of a built-in function or else it will return an error. Within the parentheses there is a parameter name and the data type of the parameter.

The entered parameters is where the function starts to be slightly different to procedures. There is only one type of entered parameter for functions and that is an IN parameter. The next difference is that a function expects a return value. After the input parameter, there is a RETURN statement along with the expected data type the value should be returned as.

The next step is to decide if you want the function to be DETERMINISTIC or not. A deterministic function will always return the same value for a given parameter. This is not something that MySQL checks, so it relies on the programmer to not have put in coding that may result in non-deterministic values. A incorrectly assigned routine may decrease performance. The default value is NOT DETERMINISTIC .

Within the body of SQL code, the main differences between a function and stored procedure are at the beginning and end. At the beginning one should declare a variable that you want to assign the result. Then at the end of the function you return that variable.

User Defined Function Example

DELIMITER $$CREATE FUNCTION convert_sqft_sqmet (area DECIMAL(10,2)) RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE square_meters DECIMAL(10, 2);
SELECT area * 0.0929 INTO square_meters;
RETURN square_meters;
END$$
DELIMITER ;

Stored Routines are an efficient way of being able to use the same code or SQL queries over and over again, saving the time it would require to write everything out each time. I hope this post has helped your understanding of using stored routines. Thanks for reading.

For more documentation on stored routines:

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store