How to Create and Call a Stored Procedure in SQL?

Creating and calling stored procedures in SQL is a fundamental skill for database developers and administrators. Stored procedures allow you to group a set of SQL statements into a reusable and modular unit, enhancing code organization, maintainability, and performance. In this article, we will look at the process of writing and calling stored procedures in SQL, covering a variety of topics and best practises.

Introduction:

A stored procedure is a set of SQL statements that has been precompiled and is saved in the database server. They are often written in a database-specific procedural language, such as PL/SQL for Oracle or T-SQL for Microsoft SQL Server.

Stored procedures offer several benefits, including:

Code reusability: Because stored procedures are database-stored, they can be called from many applications or scripts, facilitating code reuse and standardisation.

Performance optimization: Stored procedures are precompiled and optimized, resulting in improved query execution time and reduced network traffic.

Enhanced security: Stored procedures can encapsulate complex logic and access control mechanisms, providing an additional layer of security by restricting direct access to underlying tables.

Modular code organization: By grouping related SQL statements into a stored procedure, you can achieve modular code organization, making code maintenance and debugging more manageable

Note : We are making use of MYSQL work bench to create stored procedures.

Creating Stored Procedure :

Creating a stored procedure involves defining its name, parameters (if any), and the SQL statements it will execute. The syntax may differ significantly depending on the database system, but the essential ideas stay the same.

a. Syntax:

The basic syntax for creating a stored procedure is as follows:

The CREATE PROCEDURE statement is used to create a new stored procedure with the specified name. The AS keyword is followed by the BEGIN and END keywords, which enclose the SQL statements that constitute the body of the stored procedure.

b. Parameters:

Stored procedures can accept parameters, which allow you to pass values into the procedure for processing. Parameters are declared within the parentheses after the procedure name.

The parameters can have three different modes:

IN: The parameter is read-only within the stored procedure and cannot be modified.

OUT: The parameter is write-only within the stored procedure and can be modified. The modified value is passed back to the calling code.

INOUT: The parameter is both readable and writable within the stored procedure. The modified value is passed back to the calling code.

Each parameter is declared with a name, followed by its data type.

c. Variables:

Inside a stored procedure, you can declare variables to store intermediate results or temporary values. Variables are declared using the DECLARE keyword followed by the variable name and data type.

d. SQL Statements:

The body of a stored procedure consists of SQL statements that define the procedure's behavior. You can include any valid SQL statements within the procedure, such as SELECT, INSERT, UPDATE, DELETE, and control flow statements like IF, WHILE, etc.

e. Control Flow and Error Handling:

Stored procedures can include control flow statements, such as IF, WHILE, and CASE, to implement conditional logic and looping. Error handling can also be incorporated using TRY-CATCH blocks to handle exceptions gracefully.

f. Comments:

Adding comments to your stored procedures is a good practice for improving code readability and maintenance. In SQL, you can add comments using the -- or /* */ syntax.

Example:

Now let's say out database consists of students table with following values

As mentioned earlier we are making use of MYSQL workbench

Step 1 :

Head out to navigation section and right-click on stored procedures and select create stored procedure

Step 2 :

A window appears with all default options selected and some pre-built snippet of procedure Make changes accordingly and click on apply changes

Let us create a procedure studentsAge()

In this syntax, the studentAge() is the name of the stored procedure. The BEGIN and END keywords are unnecessary when accompanying a single statement in a stored procedure. However, including them to make the code more clear is a good idea.

When we run this statement and everything checks out, we will see the following message: "Commands completed successfully." It means that the stored procedure was successfully compiled and saved to the database.

Calling stored Procedures in sql :

A stored procedure can be called by using the CALL statement. This statement's parameters (IN, OUT, or INOUT) return the values to its caller. The stored procedure in MySQL is called using the syntax shown below: Syntax :

We'll call the above created procedure in our MYSQL server

Now a stored procedure inside our database is called and the resultant output obtained is students table ordered according to their respective ages :

Conclusion:

Stored procedures are a powerful tool for database developers and administrators to efficiently organize and execute SQL code. They offer improved performance, code reusability, security, and simplified maintenance. You can create reliable and efficient stored procedures by following best practices and incorporating error handling and transaction management.

This article covers all the required steps for creating and calling stored procedures with suitable example.