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.
Stored procedures might sound like a complex term, but they are fundamental to efficient database management. Let's start with its definition.
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:
These components come together to create a reusable and efficient way of executing operations on a database.
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.)
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:
These three benefits show why you should use stored procedures.
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:
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.
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.
Now let’s look at useful commands that pair with stored procedures.
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:
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;
```
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;
```
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;
```
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.
We’re going to look at creating and using stored procedures in three areas:
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:
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)
);
```
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);
```
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 ;
```
To call the stored procedure and retrieve employees from a specific department, use the CALL statement.
```
CALL GetEmployeesByDepartment(1);
```
In SQL Server, stored procedures are created an executed slightly differently, but without much change. Here's an example:
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)
);
```
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);
```
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;
```
To execute the stored procedure and retrieve employees from a specific department, use the EXEC statement.
```
EXEC GetEmployeesByDepartment @DepartmentID = 1;
```
Oracle also supports stored procedures. Here is a step-by-step guide on how to implement them in Oracle using SQL.
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)
);
```
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);
```
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;
```
Wrapping up this hands-on intro, let’s look at some best practices for designing your stored procedures.
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;
```
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.)
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.
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:
Output:
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:
(Related reading: source code management.)
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.
See an error or have a suggestion? Please let us know by emailing ssg-blogs@splunk.com.
This posting does not necessarily represent Splunk's position, strategies or opinion.
The Splunk platform removes the barriers between data and action, empowering observability, IT and security teams to ensure their organizations are secure, resilient and innovative.
Founded in 2003, Splunk is a global company — with over 7,500 employees, Splunkers have received over 1,020 patents to date and availability in 21 regions around the world — and offers an open, extensible data platform that supports shared data across any environment so that all teams in an organization can get end-to-end visibility, with context, for every interaction and business process. Build a strong data foundation with Splunk.