SQL Stored Procedure: Automate and Optimize Queries

SQL stored procedures are sets of SQL statements saved and stored in a database. They can be executed on demand to perform data manipulation and validation tasks, reducing the need to write repetitive SQL code for common operations. Stored procedures are helpful in database management by promoting efficiency and reusability. In addition, they support enhanced database security and maintainability. In this article, we will discuss how to create and execute SQL stored procedures, common use cases, and best practices.

As we get started, I highly recommend taking DataCamp’s Introduction to SQL and Learn SQL courses to learn the foundational knowledge of extracting and analyzing data using SQL. Also, the SQL Basics Cheat Sheet, which you can download, is a helpful reference because it has all the most common SQL functions.

What is Stored Procedure in SQL?

A stored procedure in SQL is a collection of SQL statements saved and stored within the database. The purpose of the SQL stored procedure is to perform a sequence of operations on a database, such as querying, inserting, updating, or deleting data.

Unlike regular SQL queries, executed as separate commands, stored procedures encapsulate a set of SQL statements, making it easy to reuse the code without having to write SQL commands repeatedly.

The benefits of SQL stored procedures include the following:

• Code reusability: Once a stored procedure is created, it can be called as many times as needed, eliminating redundancy in SQL code.
• Enhanced performance: Stored procedures often execute faster because they are precompiled and stored on the database server, reducing network latency and compilation time.
• Security: Stored procedures can improve data security and control over sensitive data access by granting users permission to execute a stored procedure without direct access to tables.

Basic syntax and structure

The syntax for creating a stored procedure may vary slightly depending on the database system (e.g., MySQL, SQL Server, Oracle). Below is a general example using SQL Server syntax:

-- Create a stored procedure named ProcedureName
CREATE PROCEDURE ProcedureName 
    @Parameter1 INT, 
    @Parameter2 VARCHAR(50)  
AS
BEGIN
    -- SQL statements go here
    SELECT * FROM TableName WHERE Column1 = @Parameter1 AND Column2 = @Parameter2;
END;

In the above syntax;

• CREATE PROCEDURE: This command is used to define a new stored procedure.

• ProcedureName: The name given to the stored procedure. It should be unique within the database.

• @Parameter1, @Parameter2: Parameters are optional; they allow the procedure to receive data inputs. Each parameter is defined with a @ symbol and a data type (e.g., INT, VARCHAR(50)).

• AS BEGIN...END: The SQL statements within BEGIN and END form the body of the procedure, where the main logic is executed. The procedure retrieves records from a table based on specific conditions in this example.

Input and output parameters

The input and output parameters allow you to pass values to and from a stored procedure.

For example, if @Parameter1 is defined as an input parameter, it can be assigned any value when the procedure is called, affecting the SQL logic or output. In the example below, the @UserID parameter retrieves data specific to the provided UserID.

-- Create a procedure to retrieve data for a specific user by UserID
CREATE PROCEDURE GetUserData 
 -- Input parameter: ID of the user to retrieve
    @UserID INT 
AS
BEGIN
    -- Select all columns from Users where UserID matches the input parameter
    SELECT * FROM Users WHERE UserID = @UserID;
END;

Output parameters, defined by the OUTPUT keyword, allow a stored procedure to send a value back to the calling environment. For example, if a procedure calculates a discount, it could use an output parameter to pass it back to the calling program.

-- Create a procedure to calculate the discounted price
CREATE PROCEDURE CalculateDiscount 
    @Price DECIMAL(10, 2), 
    @DiscountRate DECIMAL(5, 2),  
    @FinalPrice DECIMAL(10, 2) OUTPUT -- Output: final price after discount
AS
BEGIN
    -- Calculate final price by applying the discount rate to the original price
    SET @FinalPrice = @Price * (1 - @DiscountRate);
END;

To call this procedure, you would use;

-- Declare a variable to store the final price after discount
DECLARE @FinalPrice DECIMAL(10, 2);
-- Execute the CalculateDiscount procedure with a price of 100 and a 10% discount 
-- Store the output in the @FinalPrice variable
EXEC CalculateDiscount @Price = 100, @DiscountRate = 0.1, @FinalPrice = @FinalPrice OUTPUT;
-- Select and display the final discounted price
SELECT @FinalPrice AS FinalPrice;

Stored Procedures in MySQL

As I had mentioned, executing a stored procedure in SQL can be done in different ways depending on the database system and the tools used.

Creating stored procedures in MySQL

Creating a stored procedure in MySQL involves defining the procedure's name, parameters, and the SQL statements that make up its body. The following example creates a procedure called GetEmployeeDetails that takes EmployeeID as an input parameter and retrieves details for that specific employee.

DELIMITER $
-- Create a procedure to retrieve details for a specific employee by EmployeeID
CREATE PROCEDURE GetEmployeeDetails(IN EmployeeID INT)  
BEGIN  
    -- Select all columns from Employees where EmployeeID matches the input parameter  
    SELECT * FROM Employees WHERE EmployeeID = EmployeeID;  
END$
DELIMITER ;

Executing stored procedures in MySQL

The most common way to execute a stored procedure is using SQL commands. In MySQL, we use the CALL command to execute the stored procedure.

CALL ProcedureName();

Using the defined GetEmployeeDetails procedure, the execution query would look like this:

-- Execute the stored procedure to retrieve details for EmployeeID 101
CALL GetEmployeeDetails(101);

Stored Procedures in SQL Server

SQL Server provides specific syntax and commands for creating, executing, and managing stored procedures. This approach makes it easier to build efficient and reusable SQL routines that can handle complex tasks with minimal repetition.

Creating stored procedures in SQL Server

Creating a stored procedure in SQL Server involves defining the procedure's name, parameters, and the SQL statements that make up its body. The following example creates a procedure called GetEmployeeDetails that takes @EmployeeID as an input parameter and retrieves details for that specific employee.

-- Create a procedure to retrieve details for a specific employee by EmployeeID
CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT          -- Input parameter: ID of the employee to retrieve
AS
BEGIN
    -- Select all columns from Employees where EmployeeID matches the input parameter
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

Executing stored procedures in SQL Server

In SQL Server, the EXEC or EXECUTE command calls a stored procedure. The following example shows how to execute the stored procedure GetEmployeeDetails using specific input parameters. 

-- Execute the GetEmployeeDetails procedure with the EmployeeIDset to 102
EXEC GetEmployeeDetails @EmployeeID = 102;

You can also execute the output parameters by declaring the variable in the command. In the following example, @TotalSales is declared a variable to receive the output from CalculateTotalSales. 

-- Declare a variable to store the total sales amount
DECLARE @TotalSales DECIMAL(10, 2);
-- Execute CalculateTotalSales for SalespersonID 5, store the result in @TotalSales
EXEC CalculateTotalSales @SalespersonID = 5, @TotalSales = @TotalSales OUTPUT;
-- Display the total sales amount
SELECT @TotalSales AS TotalSales;

I recommend taking our Introduction to SQL Server course to understand SQL Server's different functionalities for querying data. Also, consider our full SQL Server Developer career track, which will not only equip you with the skills to create, update, and execute stored procedures, but will also help you with aggregate functions, joining, inserting, and deleting tables, and much more.

Common Uses for Stored Procedures

SQL stored procedures are useful in scenarios where repetitive complex tasks are required. The following are real-world applications of stored procedures in data management and business operations. 

Data validation and integrity enforcement

Stored procedures can be used to validate data before update or insertion. In the example below, a stored procedure checks that a customer’s email is unique before inserting a new record into the Customers table, ensuring data consistency. This centralizes validation logic within the database, reducing redundancy and ensuring uniform enforcement across different applications.

-- Create a procedure to add a new customer, checking for duplicate email
CREATE PROCEDURE AddCustomer
    @CustomerName VARCHAR(50), 
    @CustomerEmail VARCHAR(50)
AS
BEGIN
    -- Check if the email already exists in the Customers table
    IF EXISTS (SELECT 1 FROM Customers WHERE Email = @CustomerEmail)
        -- Throw an error if the email is already in use
        THROW 50000, 'Email already exists.', 1;
    ELSE
        -- Insert new customer details if email is unique
        INSERT INTO Customers (Name, Email) VALUES (@CustomerName, @CustomerEmail);
END;

Automated data processing and reporting

You can also use stored procedures to generate regular reports or process large datasets. For example, a stored procedure could aggregate daily sales data from an e-commerce platform and store it in a reporting table, making it easier for teams to access sales insights without running complex queries.

-- Create a procedure to generate a daily sales report
CREATE PROCEDURE GenerateDailySalesReport
AS
BEGIN
    -- Insert today's date and total sales into the SalesReport table
    INSERT INTO SalesReport (ReportDate, TotalSales)
    
    -- Select current date and sum of sales for today from Sales table
    SELECT CAST(GETDATE() AS DATE), SUM(SalesAmount) 
    FROM Sales 
    WHERE SaleDate = CAST(GETDATE() AS DATE);
END;

Transaction management

By using stored procedures, you can ensure multiple operations are executed as a single transaction. For example, in a banking system, a stored procedure can handle both debit and credit actions in a funds transfer, making sure both actions succeed or fail together.

-- Create a procedure to transfer funds between accounts
CREATE PROCEDURE TransferFunds
    @SenderAccount INT,
    @ReceiverAccount INT,
    @Amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;  -- Start a transaction to ensure atomicity
    -- Deduct the specified amount from the sender's account balance
    UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @SenderAccount;
    -- Add the specified amount to the receiver's account balance
    UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ReceiverAccount;
    -- Check for errors and rollback if any occurred; otherwise, commit the transaction
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION;  -- Undo all changes if an error occurred
    ELSE
        COMMIT TRANSACTION;    -- Confirm changes if no errors
END;

Access control and data security

You can also use SQL stored to control data access to sensitive information. For example, a stored procedure can limit direct table access by allowing users to call a procedure that retrieves only relevant fields, such as account balances, without transaction details.

-- Create a procedure to retrieve account balance, with authorization check
CREATE PROCEDURE GetAccountBalance
    @AccountID INT, 
    @UserID INT 
AS
BEGIN
    -- Check if the account exists and is owned by the specified user
    IF EXISTS (SELECT 1 FROM Accounts WHERE AccountID = @AccountID AND UserID = @UserID)
        -- If authorized, select and return the account balance
        SELECT Balance FROM Accounts WHERE AccountID = @AccountID;
    ELSE
        -- If unauthorized, throw an error
        THROW 50000, 'Unauthorized access.', 1;
END;

Data migration and ETL processes

Stored procedures are also used to load, transform, and migrate data between systems. A stored procedure can automate data extraction from a source database, transform it as needed, and insert it into a target table, simplifying data integration for reporting or analysis.

CREATE PROCEDURE ETLProcess
AS
BEGIN
    -- Extract
    INSERT INTO StagingTable
    SELECT * FROM SourceTable WHERE Condition;
    -- Transform
    UPDATE StagingTable SET ColumnX = TransformationLogic(ColumnX);
    -- Load
    INSERT INTO TargetTable
    SELECT * FROM StagingTable;
END;

Best Practices for Stored Procedures

Writing efficient and maintainable stored procedures ensures your database performs optimally. The following are the tips for writing stored procedures for your SQL databases.

• Use Consistent Naming Conventions: To make stored procedures easy to identify and understand, use a consistent and descriptive naming format. Also, avoid the sp_ Prefix in SQL Server reserved for system procedures to prevent potential conflicts and performance issues.

• Implement Error Handling: Wrap SQL statements in TRY...CATCH blocks to catch and handle errors and maintain data integrity. 

• Optimize for Performance: Minimize the use of cursors as they can be slow and resource-intensive. Instead, try using set-based operations, which are generally more efficient. Also, index the frequently used columns and avoid complex joins in large tables to reduce memory overhead and improve efficiency.

• Parameterize Stored Procedures: Use parameters instead of hardcoded values to allow you to pass dynamic values into your procedure, making it more flexible and reusable.

Check out our Intermediate SQL course to learn more about using aggregate functions and joins to filter data. Also, try out our SQL Server Fundamentals and SQL Fundamentals skill tracks to improve your joining tables and data analysis skills.

Conclusion

SQL stored procedures enhance code reusability and performance optimization in database management. Stored procedures also enhance database security through controlled access and ensuring data integrity. As a data practitioner, I encourage you to practice creating and executing stored procedures to master the best database management practices.

If you are interested in becoming a proficient data analyst, check out our Associate Data Analyst in SQL career track to learn the necessary skills. The Reporting in SQL course is also appropriate if you want to learn how to build professional dashboards using SQL. Finally, I recommend obtaining the SQL Associate Certification to demonstrate your mastery of using SQL for data analysis and stand out among other data professionals.