SQL Stored Procedures

Stored procedures in SQL are an essential tool for automating and simplifying repetitive database tasks. They allow you to create blocks of code that can be run as needed, making working with data more efficient and convenient. Today, we’ll explore what procedures are in SQL, why they’re needed, and how to create them.

What is a stored procedure in SQL?

A stored procedure in SQL is a set of SQL commands stored on the database server and executed on request. It is created once and stored on the server, allowing it to be called multiple times, ensuring code reuse and improving database performance.

Why are stored procedures useful?

1. Performance Optimization: Stored procedures are stored and executed directly on the database side, reducing the likelihood of performance delays.
2. Simplify logic: By supporting parameters and SQL constructs such as loops and conditions, procedures allow you to organize complex data processing by reducing the work to a single command call.
3. Code reuse: A procedure created once can be called multiple times on different sets of data, reducing code duplication and making the application more robust.
4. Performance improvements: Procedures are compiled when created, so they don’t need to be recompiled each time they are called, which speeds up execution.

What is the difference between a stored procedure and a function?

Stored procedures in SQL can sometimes be confused with functions—both are used to execute logic and process data on the server side. However, they have significant differences in purpose, use, and functionality.

• Return value: A procedure can return one or more values ​​through OUTparameters, or return nothing. A function always returns a single value.
• Usage in queries: The function can be used in SELECT, WHEREand other SQL queries. The procedure cannot be used in queries; it is called separately.
• Modifying data: A procedure can modify data (e.g., perform INSERT, UPDATE, DELETE). A function typically does not modify data in tables.
• Transaction Management: Transactions can be managed in a procedure ( COMMIT, ROLLBACK). This is not supported by the function.
• Basic purpose: A procedure performs tasks and operations on data, while a function calculates and returns a result.

General syntax of procedures

It’s important to note that different DBMSs use their own syntactic features and extensions when working with SQL procedures. We’ll discuss some of these below, but for now, let’s look at the general procedure syntax.

Main components :

1. CREATE PROCEDURE: command to create a procedure.
2. procedure_name: The name of the procedure that will be used later to run it.
3. Parameters: Most DBMSs allow you to specify input and output parameters to pass data to a procedure and retrieve a result. Parameters have a direction ( IN, OUT, INOUT) and a data type.
4. Procedure body: the block where the SQL instructions and logic for the procedure are written. It may contain various functions, operators, conditional statements, and loops.

How to create a procedure in SQL

Access rights

When creating a procedure in SQL, access rights play a crucial role in ensuring data security. Most DBMSs have rights management mechanisms that allow you to restrict users’ ability to create, modify, execute, and delete procedures.

Basic access rights when creating and working with procedures:

• Permissions to create a procedure. For example, in MySQL this permission is CREATE ROUTINE, in PostgreSQL it is CREATE, and in Oracle it is CREATE PROCEDURE.
• Execute procedure permissions (e.g. EXECUTE) allow specific users or groups to execute a procedure. These permissions can be assigned using the command. GRANT EXECUTE ON procedure_name TO user_name;.
• Permissions to modify and delete the procedure. The user needs editing privileges, such as ALTERor DROP.
• Data access permissions in the procedure body. When a procedure interacts with tables and other database objects, it must be ensured that it has the permissions to perform operations on these objects (e.g., SELECT, INSERT) UPDATE.

An example of creating a procedure in MySQL

In MySQL, procedures are created using the commandCREATE PROCEDURE, followed by the procedure name and a list of parameters (if any). This is followed by the procedure body, enclosed in a block. BEGIN ... END.

DELIMITER is used to tell MySQL that the command termination character in this context will be //, not ;, since multiple statements separated by are often used within a procedure ;.

Let’s say we need to create a procedure that returns a list of all employees over a certain age from a table. employees.

IN min_age INT – An input parameter (see below) that accepts the minimum age to filter the data.

Inside the procedure, a query is executed that returns all employees whose age is greater than the value passed through the parameter.

Example of creating a procedure in PostgreSQL

In PostgreSQL, procedures are created using the command CREATE PROCEDURE, but they require a language specification (most often plpgsql).

In PostgreSQL, you can create more complex procedures, including working with variables and execution logic. Let’s look at an example procedure that counts the number of orders for a specific customer in the orders table.

• DECLARE is a variable declaration block where we declare a variable total_ordersto store the result.
• RAISE NOTICE — output a message to the log (analogous to data output).

Input and output parameters

These are the key elements in stored procedures that allow you to pass data into the procedure and return results from it.

Input parameters (IN) allow you to pass values ​​into a procedure for use in its execution logic.

For example, let’s create a procedure that takes a student ID and the number of extra points to add to the current points.

In this example:

• student_id— input parameter that determines which student to update.
• bonus_points— an input parameter indicating by how many points to increase the current ones.

Output parameters (OUT) allow a procedure to return values ​​after execution. For example, let’s create a procedure that returns a student’s name based on their ID:

In this example:

• student_id— input parameter for student search.
• student_name— output parameter where the student’s name is saved.

INOUT parameters can be either input or output, allowing data to be passed and modified.

Let’s say we have a procedure that increases a student’s score by a certain amount and returns the new score. To do this, we’ll use an INOUT parameter that accepts the original score and returns the updated score.

• student_id— INOUT parameter that passes the student ID to the procedure and can also be used to return changes (for example, after updating scores).
• current_score— INOUT parameter that passes the current student score and is updated with new scores during the procedure.
• bonus— IN parameter that passes the number of points to increase.

Starting the procedure

Running a procedure is the process that begins after it has been called and involves several steps, from passing parameters to executing instructions and returning results.

To run a procedure in SQL, the command is typically used. CALL:

Let’s look at starting a procedure using the previous example – the procedure. update_student_score:

1. Passing parameters: We call the procedure CALL update_student_score(1, 50, 10);by passing student_id = 1, current_score = 50, and bonus = 10.
2. Executing the procedure logic: The procedure finds the student with ID 1 and updates his score by adding current_scorethe value to bonus. The score becomes 60.
3. Returning a new value : current_scoreThe new value 60 is written to the updated variable and returned from the procedure.

Deleting a stored procedure

Dropping a stored procedure is the process of removing the procedure itself from the database. Once deleted, it is no longer available for execution.

In PostgreSQL, MySQL, and SQL Server, the command is used for this purpose. DROP PROCEDURE.

• IF EXISTS— an optional parameter that allows you to avoid an error if a procedure with the same name does not exist.
• procedure_name— the name of the procedure to be deleted.
• parameter_types— an optional list of parameter types (if the procedure is overloaded, then parameters must be specified for correct deletion).

Changing procedures

Modifying stored procedures in SQL usually requires dropping them and creating new versions, as most DBMSs do not support direct modification.

• In MySQL DROP PROCEDURE,, is used and then a new version of the procedure is created with the necessary changes.
• In PostgreSQL, you can use the command CREATE OR REPLACE PROCEDUREto avoid dropping the procedure manually.
• In SQL Server, to modify an existing procedure, the command is used. ALTER PROCEDURE.

Conclusion

Stored procedures in SQL are a powerful tool that helps automate tasks and improve database performance. They simplify repetitive operations and make code cleaner and more maintainable. Now that you know the basics of working with stored procedures in SQL, you can use them to improve your database workflow and optimize complex operations!