SQL: Stored Routines

What are Stored Routines?

Stored Routines are prepared pieces of SQL code that are saved to a specific database server. By storing this code, it can be called by the user over and over again without having to continuously rewrite the entire code, which is why they are so useful in writing more efficient code. If, as an employee, you are required to go over the results of a certain query often, it is faster to write a stored procedure and call it when needed instead of writing out the entire code each time.

Procedure Syntax

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

Procedure Parameters

As mentioned above, procedures can have one or more parameters. There are two types of parameters: IN and OUT . IN is a parameter that enters a value into the procedure. The OUT parameter is a value that is passed out from the procedure to the individual calling on it. This is necessary if you need the results to be saved in an outside variable. If the type of parameter is not specified, the default is an IN parameter. In any case, it is best to fully write out the types of parameter.

Procedure Example

Let’s go through an example of a created procedure. The database is for an online store seen in my previous posts. Perhaps we want to see how many clients the store has in a particular State. To do this we would want to be able to enter a specific value for a State and see the total number of clients in that State returned.

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

User Defined Functions Syntax

The syntax for functions is slightly different to procedures.

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 ;

User Defined Function Example

In this case, we will give a simple example of converting metrics. Let’s say we needed to convert an area value from square feet to square meters.

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 ;

--

--

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