What Are Stored Procedures?

SQL, or Structured Query Language, is commonly used for manipulating data in databases. One common feature of SQL is stored procedures. Stored procedures can greatly improve the efficiency, security, and functionality of your database.

But how do they actually work? And why should you use them?

In this guide, we'll discuss what stored procedures are, how to create them, and some best practices for using them.

Introduction to Stored Procedures

Stored procedures might sound like a complex term, but they are fundamental to efficient database management. Let's start with its definition.

What are Stored Procedures?

Stored procedures are essentially sets of SQL commands that are saved and executed on the database server. Instead of sending multiple individual queries, you can call a single stored procedure that executes a pre-defined set of operations.

The basic syntax for creating a stored procedure in SQL Server is:

CREATE PROCEDURE procedure_name
AS
BEGIN
   -- SQL statements
END

Here are some key components of a stored procedure:

• Input parameters are variables that can be passed to the stored procedure from outside.
• Output parameters are similar to input parameters, but they return values instead of receiving them.
• Variables are local variables used within the stored procedure for calculations or temporary storage.
• SQL statements are the actual queries and commands that make up the logic of the stored procedure.

These components come together to create a reusable and efficient way of executing operations on a database.

How stored procedures work

Stored procedures are executed on the server side, which means that they can perform operations much faster than if you were to send multiple queries from the client side. This also reduces network traffic, as only the result of the stored procedure is returned instead of sending back each query individually.

(Related reading: server-side rendering.)

Role in database management

Stored procedures play a crucial role in database management by centralizing logic within the database itself. This means that critical operations are performed consistently, securely, and efficiently. They help to:

• Maintain data integrity.
• Enforce business rules.
• Reduce the complexity of database interactions.

Benefits of using stored procedures

These three benefits show why you should use stored procedures.

Enhanced performance

One of the primary benefits of stored procedures is their ability to boost database performance. Since they are precompiled, stored procedures execute faster than ad-hoc SQL queries. This leads to:

• Quicker response times
• More efficient use of server resources

Reusability and maintainability

Since stored procedures can be called multiple times, they promote code reusability. This reduces the need to repeat code throughout different applications, leading to easier maintenance and updates. Additionally, changes made to a stored procedure will automatically be reflected in all applications that use it.

This helps ensure consistency and reduces the potential for errors.

Data security

Stored procedures also play a crucial role in data security. This can be done by controlling access to the database through stored procedures.

Organizations can restrict user permissions and limit their ability to directly interact with tables or views. Therefore, anyone who interacts with the database can only go through the standard approved stored procedure. This adds an extra layer of protection against unauthorized access or malicious attacks on your database.

Common commands used with Stored Procedures

Now let’s look at useful commands that pair with stored procedures.

`CREATE PROCEDURE`

As mentioned earlier, this command is used to define a new stored procedure in the database.

Here's an example of a stored procedure using this function:

Let's say we have a table called Employees with the following columns:

• EmployeeID
• FirstName
• LastName
• DepartmentID
• Salary

We want to create a stored procedure that retrieves all employees belonging to a specific department.

CREATE PROCEDURE GetEmployeesByDepartment
   @DepartmentID INT
AS
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = @DepartmentID;
END;

`EXEC`

This command is used to execute a stored procedure. It can also be used to pass input and output parameters.

For our previous example, the `EXEC` command would look like this:

EXEC GetEmployeesByDepartment @DepartmentID = 1;

`ALTER PROCEDURE`

This command allows you to make changes to an existing stored procedure without deleting and recreating it.

Continuing with the previous example, let's say we want to modify the `GetEmployeesByDepartment` stored procedure to include an additional filter for salary. Specifically, we want to retrieve employees from a specific department who earn above a specified salary.

Here's an example:

ALTER PROCEDURE GetEmployeesByDepartment
   @DepartmentID INT,
   @MinSalary DECIMAL(10, 2)
AS
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = @DepartmentID AND Salary > @MinSalary;
END;

`DROP PROCEDURE`

If you no longer need a stored procedure, you can use this command to delete it from the database.

Here's an example: To delete the stored procedure, use the DROP PROCEDURE command followed by the name of the stored procedure.

DROP PROCEDURE GetEmployeesByDepartment

This drops the stored procedure that we created previously.

How to create & use stored procedures

We’re going to look at creating and using stored procedures in three areas:

• MySQL
• SQL Server
• Oracle

MySQL

Creating a stored procedure in MySQL is rather straightforward. You define the procedure using the `CREATE PROCEDURE` statement, specify the parameters, and write the SQL code.

Here is how you can do it:

Step 1: Create the employees table

First, let's create a sample Employees table to fill in data for us to work with.

CREATE TABLE Employees (
   EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
   FirstName VARCHAR(50),
   LastName VARCHAR(50),
   DepartmentID INT,
   Salary DECIMAL(10, 2)
);

Step 2: Insert sample data

Insert some sample data into the Employees table.

INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary)
VALUES
('John', 'Doe', 1, 60000),
('Jane', 'Smith', 2, 65000),
('Sam', 'Brown', 1, 62000),
('Sue', 'Green', 3, 67000);

Step 3: Create a stored procedure

Let's create a stored procedure that retrieves employees based on their department.

DELIMITER //
CREATE PROCEDURE GetEmployeesByDepartment(IN depID INT)
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = depID;
END //
DELIMITER ;

Step 4: Call the stored procedure

To call the stored procedure and retrieve employees from a specific department, use the CALL statement.

CALL GetEmployeesByDepartment(1);

SQL Server

In SQL Server, stored procedures are created an executed slightly differently, but without much change. Here's an example:

Step 1: Create the employees table

First, let's create a sample Employees table.

CREATE TABLE Employees (
   EmployeeID INT PRIMARY KEY IDENTITY(1,1),
   FirstName NVARCHAR(50),
   LastName NVARCHAR(50),
   DepartmentID INT,
   Salary DECIMAL(10, 2)
);

Step 2: Insert sample data

Next, we will insert some sample data into the Employees table.

INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary)
VALUES
('John', 'Doe', 1, 60000),
('Jane', 'Smith', 2, 65000),
('Sam', 'Brown', 1, 62000),
('Sue', 'Green', 3, 67000);

Step 3: Create the stored procedure

Let's create a stored procedure that retrieves employees based on their department.

CREATE PROCEDURE GetEmployeesByDepartment
   @DepartmentID INT
AS
BEGIN
   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
   FROM Employees
   WHERE DepartmentID = @DepartmentID;
END;

Step 4: Execute the stored procedure

To execute the stored procedure and retrieve employees from a specific department, use the EXEC statement.

EXEC GetEmployeesByDepartment @DepartmentID = 1;

Oracle

Oracle also supports stored procedures. Here is a step-by-step guide on how to implement them in Oracle using SQL.

Step 1: Create the employees table

First, let's create a sample Employees table.

CREATE TABLE Employees (
   EmployeeID NUMBER PRIMARY KEY,
   FirstName VARCHAR2(50),
   LastName VARCHAR2(50),
   DepartmentID NUMBER,
   Salary NUMBER(10, 2)
);

Step 2: Insert sample data

Next, we insert some sample data into the employees table to create a dataset.

INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID, Salary)
VALUES
(1, 'John', 'Doe', 1, 60000),
(2, 'Jane', 'Smith', 2, 65000),
(3, 'Sam', 'Brown', 1, 62000),
(4, 'Sue', 'Green', 3, 67000);

Step 3: Create the stored procedure

Let's create a stored procedure that retrieves employees based on their department.

CREATE OR REPLACE PROCEDURE GetEmployeesByDepartment (
   p_DepartmentID IN NUMBER
)
AS
BEGIN
   FOR r IN (SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
             FROM Employees
             WHERE DepartmentID = p_DepartmentID)
   LOOP
       DBMS_OUTPUT.PUT_LINE('EmployeeID: ' || r.EmployeeID ||
                            ', FirstName: ' || r.FirstName ||
                            ', LastName: ' || r.LastName ||
                            ', DepartmentID: ' || r.DepartmentID ||
                            ', Salary: ' || r.Salary);
   END LOOP;
END;

Designing stored procedures: best practices

Wrapping up this hands-on intro, let’s look at some best practices for designing your stored procedures.

Use parameterized queries

Parameterized queries within stored procedures help prevent SQL injection attacks. Always use parameters instead of concatenating user input directly into SQL statements.

For example, instead of using this:

SELECT * FROM Products WHERE ProductID = '101';

Use this:

DECLARE p_ProductID INT;
SET p_ProductID = 101;
SELECT * FROM Products WHERE ProductID = p_ProductID;

Limit access to underlying tables

As mentioned earlier, stored procedures can act as a security layer by restricting direct access to the underlying tables. This reduces the risk of sensitive data being exposed.

(Related reading: identity access management & the principle of least privilege.)

Optimize SQL code

Ensure your stored procedures are optimized for performance. In addition, avoid unnecessary computations and use indexing effectively. Analyze execution plans to identify bottlenecks and improve query efficiency.

For example, instead of using `SELECT *` to retrieve all columns from a table, specify only the necessary columns to reduce data transfer and improve performance.

Document your stored procedures

It's important to document your code, even for stored procedures. This helps other developers understand the purpose and functionality of each procedure. It also promotes consistency in naming conventions and coding styles.

This can be done through comments within the stored procedure or by maintaining a separate documentation file.

For example:

-- This stored procedure retrieves all orders for a specific customer
CREATE PROCEDURE GetCustomerOrders
   @CustomerID INT
AS
BEGIN
   SELECT * FROM Orders WHERE CustomerID = @CustomerID;
END
/*
Stored Procedure Name: GetCustomerOrders
Purpose: Retrieve all orders associated with a given customer ID.
Input Parameters:
CustomerID: The ID of the customer whose orders are being retrieved.
Output: A list of order records from the Orders table
*/

Maintain version control

Version control is vital for managing changes to stored procedures. Have a repository of stored procedure scripts and document changes thoroughly. This practice helps track modifications and ensure consistency across deployments.

This can be done by using a version control system (VCS) such as:

• Git
• SVN
• Team Foundation Version Control (TFVC)
• Mercurial

(Related reading: source code management.)

Final thoughts

Stored procedures are a powerful and secure way to manage databases. They bring several benefits that, when combined with proper adherence to best practices, can boost the effectiveness of data analysis within the organization.