What is a stored procedure?

Benefits of using stored procedures

One key benefit of using stored procedures is code reusability. Storing frequently used SQL queries as stored procedures eliminates the need to rewrite them. Any user or application with the proper authority can access and execute that code as needed. Also, because the statements in a stored procedure need only be written once, they help to improve user productivity.

Encapsulating queries as stored procedures also decreases the potential for code inconsistencies and errors. Equally important, the query processor can process the procedure faster because it doesn't have to create a new plan every time the code is to be executed.

A stored procedure provides an important layer of security between the user interface and the database. It protects the underlying database objects, and controls what processes and activities users can perform on those objects. Users also cannot write procedures, search for critical data, see the database object names or insert commands into the statements inside the procedure. These controls help to protect the database against attacks like SQL injection. The source code can be obfuscated to further strengthen security by encrypting the stored procedures. A stored procedure also preserves data integrity because information is entered in a consistent manner.

Stored procedures offer advantages over embedding queries in a graphical user interface (GUI). Since stored procedures are modular, they are easier to troubleshoot when a problem arises in an application. Stored procedures are also tunable, which eliminates the need to modify the GUI source code to improve its performance. Coding stored procedures is easier than building a query through a GUI.

When database layouts, relationships or processes change, only the procedures must be updated. Since applications don't have to know about these changes, this simplifies code maintenance.

Using stored procedures can reduce network traffic between clients and servers because the commands are executed as a single batch of code. This means that only the call to execute the procedure is sent over a network, instead of every single line of code being sent individually.

Why use stored procedures?

Stored procedures are particularly useful if a particular task is to be performed repeatedly. An example of such a task might be to migrate data from one database table to another. The procedure contains SQL statements compiled into a single execution plan and stored in the database. The procedure will be executed to perform that task each time it is required, removing the need to rewrite the code multiple times.

Stored procedures are also useful for executing multiple database operations in a single call. They can also insert, retrieve, modify, update or delete data in a database table. There's no need to rewrite entire SQL commands to perform these actions.

Stored procedure in SQL

Stored procedures in Microsoft SQL Server can accept input parameters -- one or more -- and return multiple values of output parameters. Stored procedures' program statements perform operations in the database and return a status value to a calling procedure or batch.

The syntax of SQL stored procedures looks like this:

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

There are three main types of stored procedures in SQL:

• System procedures. Also known as system-stored procedures, they start with the prefix sp_ and are physically stored in an internal, hidden-resource database. These procedures typically support SQL Server functions and can perform many types of tasks. They appear in the sys schema of each system and in a user-defined database.
• User-defined procedures. They are re-created in a user-defined database or all system databases, except when a read-only or resource database is used. They are developed in Transact-SQL (T-SQL) or are a reference to Microsoft. It's important not to use the prefix sp_ for user-defined procedures since this prefix is only used for system procedures.
• Temporary procedures. They are stored in tempdb, and there are two types: local and global. Local procedures are only visible to the current user connection, while global procedures are visible to any user after they are created. Additionally, local procedures are deleted when the connection is closed, while global stored procedures are deleted when the last session using that procedure ends.

Stored procedures in Oracle

Oracle's database language, PL/SQL, is made up of stored procedures that can be used to build applications within Oracle's database. PL/SQL components are standalone procedures, functions and packages. These components are collectively known as stored procedures and are stored in the database.

IT professionals use stored subprograms like functions and procedures in Oracle's database to properly write and test code. Once compiled, those programs become stored procedures. When invoked, stored procedures can accept parameters and be referenced by applications connected to the Oracle database.

A stored procedure in Oracle follows the basic PL/SQL block structure, which consists of declarative, executable and exception handling parts.

Stored procedure vs. function

Stored procedures and functions can be used to accomplish the same task. Both can be custom-defined as part of any application, but functions are designed to send their output to a query or T-SQL statement. Stored procedures are designed to return outputs to the application. They can return multiple values to the calling program as output parameters.

Stored procedures run as a unit and are executed repeatedly to perform the same action, which reduces the time needed to perform that action. Also, since stored procedures are created once and called multiple times, they can be used to build modular programs. Functions, on the other hand, perform several actions serially.

A user-defined function returns table variables and cannot change the server environment or operating system environment. It must have at least one parameter and return a result. In contrast, stored procedures need not return results and can modify database objects. Procedures can have either input or output parameters; functions can only have input parameters.

Stored procedures can call other procedures and perform other operations in a database since they contain programming statements, such as calling a function. Functions cannot call procedures. Furthermore, stored procedures can return a status value to a calling program, along with a reason if the call failed.

NoSQL systems are becoming more prevalent in the cloud, with numerous cloud providers and vendors offering them. Learn about the various types of NoSQL databases and their pros and cons.