Stored Procedures [Definition, Syntax, Implementation]

Posted in

Stored Procedures [Definition, Syntax, Implementation]

Maaz Bin Asad
Last updated on June 23, 2022

    One of the best ways to save time when writing code in a database management system is to use stored procedures. A stored procedure is SQL code that we can save in a database and reuse when required. The primary goal of creating a stored procedure in a database is to perform one or more DML operations on a database. In this article, we will discuss what exactly a stored procedure is along with its syntax and implementation. Also, we shall walk you through the differences between stored procedures and user-defined functions. Also, you will get familiar with all the key advantages and disadvantages of stored procedures. So, without further ado, let us begin!

    What are Stored Procedures?

    A stored procedure in a database is a collection of SQL statements stored in the database data dictionary. In other words, we can define a stored procedure as a subroutine available to applications that access a database management system (DBMS). We can also refer to a stored procedure as storp, StoredProc, StoreProc, proc, sp, SP, sproc, and StoPro. The flow control statements in a stored procedure include IF, WHILE, LOOP, CASE, REPEAT, BEGIN-END, RETURN, WAITFOR, etc. Stored procedures can save the time and memory required for executing multiple SQL statements. They also support the concept of nesting and thus we can implement a nested stored procedure by executing one stored procedure within another.  A stored procedure may return the result sets of the select statement. We can process such result sets of stored procedures by using cursors, associating a result-set locator, applications, or using other stored procedure(s). In addition, we can pass parameters to a stored procedure, and it act based on the parameter values.

    Syntax of Stored Procedures

    The following is the syntax of a stored procedure:

    CREATE or REPLACE PROCEDURE name(parameters) IS variables; BEGIN //statements; END;

    Parameters in stored procedures pass values to the procedure. The following are the three different types of parameters supported by a stored procedure:

    • IN: This parameter always receives a value from the calling program and is the default parameter to a procedure.
    • OUT: It sends a value to the calling program.
    • IN-OUT: This parameter receives and sends a value from and to the calling program.

    Implementation of Stored Procedures

    Stored procedures are analogous to user-defined functions (UDFs). The primary difference between a stored procedure and a user-defined functions is that we can execute a user-defined function like any other expression in the SQL statement. In contrast, we need to invoke stored procedures using the CALL statement, as shown below:

    CALL procedure(...)

    or

    EXECUTE procedure(...)

    The implementation of stored procedures varies from one relational database management system to another. For instance, the implementation of a stored procedure in MariaDB varies from the implementation in Microsoft SQL Server. Depending upon the database system, we can implement a stored procedure in a variety of programming languages, such as Java, C, and C++. The below table highlights some popular database systems and their supported programming languages:

    Database System Supported Programming Languages
    Microsoft SQL Server Transact-SQL and various .NET Framework languages
    MySQL SQL/PSM
    Oracle PL/SQL or Java
    PostgreSQL PL/pgSQL. It can also support PL/Perl or PL/PHP languages.
    SAP HANA SQL Script or R
    SAP ASE Transact-SQL
    SAP SQL Anywhere Transact-SQL, Watcom SQL

    Stored Procedures vs User-Defined Functions (UDFs)

    The following table highlights the key differences between stored procedures and user-defined functions (UDFs):

    Stored Procedures User-Defined Functions (UDFs)
    The return value in a stored procedure is optional. It may return zero or more values. The return value in UDFs is mandatory.
    We can call a function from a procedure. We cannot call procedures from UDFs.
    Stored procedures have input and output values. User-defined functions only have input values.
    They support try-catch blocks. They do not support try-catch blocks.
    We can execute a stored procedure using CALL or EXECUTE commands. To execute a user-defined function, it should be part of the SQL statement.
    Stored procedures support transaction management. User-defined functions (UDFs) do not support transaction management.

    Advantages and Disadvantages of Stored Procedures

    Let us now shed light on some significant advantages and disadvantages of stored procedures.

    Advantages

    • Better Performance: Since a stored procedure is compiled once and stored in an executable form, the procedure calls are faster and efficient. Therefore, the response is also quick.
    • Higher Productivity: We only need to write the SQL statements or code in a stored procedure once. This results in higher productivity since there is no need to write a piece of code again and again.
    • Reduced Traffic: All the SQL statements in a stored procedure are implemented as a single batch of code. As a result, the stored procedure significantly reduces the network traffic between the application and the database server. Instead of transmitting each SQL statement over a network, the application only needs to transmit the name of a stored procedure with its parameters.
    • Reusable: A stored procedure created once can be reused whenever required. Reusing a stored procedure prevents unnecessary rewrites of the same code in a database.
    • Modular Nature: A stored procedure is modular in nature, thus making it easier to troubleshoot a stored procedure if there is an issue in an application.
    • Security: Stored procedures provide a security layer between the database and the user interface. We can pass data as parameters in a stored procedure, so it avoids SQL injection .

    Disadvantages

    • It becomes challenging to track changes made to a stored procedure using version control .
    • Testing a logic encapsulated inside a stored procedure is difficult. Therefore, any data errors in a stored procedure are not generated until runtime.
    • When we change our database provider, we need to rewrite all the existing stored procedures since stored procedure languages are vendor-specific.
    • Some companies do not allow developers to access their databases. Therefore, they require a separate database administrator, which increases the cost.

    Conclusion

    A stored procedure is ideal to use when we need to automate a task that requires multiple SQL statements and implement that task frequently. Also, it ensure higher productivity and reduced network traffic, and thus make it easier for us to develop robust database applications.  We hope that this article helped you develop a better understanding of stored procedures. If you have any doubts or suggestions, share them with us in the comment section below.

    People are also reading:

    Leave a Comment on this Post

    0 Comments