Learn with Kareem
How to Automate Database Tasks with SQL Stored Procedures

How to Automate Database Tasks with SQL Stored Procedures

Adeyemo Kareem Adekunle's photo
Adeyemo Kareem Adekunle
·

5 min read

Table of contents

Every day, organizations generate enormous amounts of data. Depending on the type of business or industry, this data could range from sales transactions and customer browsing behavior to supply chain logistics, social media interactions, and even sensor readings from IoT devices. Managing this data efficiently often involves handling repetitive tasks that can be time-consuming and error-prone if done manually. This is where automation comes in. One powerful tool for automating tasks in SQL databases is stored procedures.

In this article, we’ll explore what stored procedures are, why they’re important, and how they can simplify database operations. By the end, you'll have a basic understanding of how to use stored procedures to automate tasks and improve efficiency.

What are Stored Procedures?

Stored procedures are a set of precompiled SQL commands saved in a database. They are designed to perform specific tasks, often involving multiple steps, in an automated way.

When a stored procedure is precompiled, the SQL statements inside it are parsed, optimized, and transformed into an execution plan by the database server before the procedure is called. This means the database doesn't have to go through the parsing and optimization process again every time the procedure is executed.

For example, let’s say you're managing a library database. Every time a student borrows or returns a book; you need to update the database. Instead of manually writing SQL commands each time, you can create a stored procedure that automates the process, saves you time, and reduces the risk of errors.

Why are Stored Procedures Important?

Stored procedures are an important feature in database management systems and offer several benefits that enhance performance. Here are some of the importance of stored procedures:

1. Automation of Repetitive Tasks

Stored procedures save time and reduce manual effort, especially in organizations with frequent database interactions.

2. Improved Performance

Since they are a precompiled set of instructions and cached in the database, stored procedure reduces execution time compared to running ad hoc SQL queries.

3. Code Reusability

Store procedures can be called to action multiple times without re-writing the code or query.

4. Error Handling

Stored procedures allow for structured error handling using blocks such as the TRY...CATCH, blocks (e.g., checking if a department exists).

Steps to Create Stored Procedures

Creating a stored procedure involves 3 key steps:

Step 1: Define the Problem

  • What task do you want to automate?

  • What inputs will your procedure need? For instance, a student’s name or the title of a book.

  • What result do you expect? For example, a success message after a task is completed.

Step 2: Write the Procedure

Write the SQL code that will perform the task. Include:

  • Logic: Use SQL commands like IF, CASE, or loops to define the procedure’s behavior.

  • Error Handling: Use TRY...CATCH blocks to manage errors gracefully.

  • Parameters: Accept inputs using placeholders (e.g., @BookID or @StudentName).

Step 3: Test and Optimize

  • Test your stored procedure with real data.

  • Use clear, descriptive names for the procedure and parameters.

  • Add comments to explain each section of the code.

Types of Stored Procedures

1. With Parameters

This procedure allows users to input values to customize their behavior. Example: Accepting a book title and author to add new books to the book table. Parameters are denoted using the @ symbols.

In the traditional method of adding a new book, we manually insert data into the appropriate table. For non-technical users, this process can be challenging and stressful due to their unfamiliarity with writing SQL queries. However, the stored procedure simplifies this task by automating the process, eliminating the need for non-technical users to write code.

INSERT INTO BOOKS (
                   BOOK_ID, FACULTY_ID, DEPT_ID, ISBN, 
                   TITLE, AUTHOR, PUBLISHER, YEAR, QUANTITY
                   )  

VALUES (1, 3, 4, 35664, 'INTRODUCTION TO SCIENCE', 'James Bond', 'INEC', 2005, 2);

With a stored procedure, users only need to provide the necessary details as input, and the procedure handles the rest behind the scenes, ensuring accuracy and efficiency. This greatly reduces the effort required and minimizes the risk of errors.

GO
CREATE PROC spAddNewBook 
    @BOOK_ID INT,
    @FACULTY_ID INT,
    @DEPT_ID INT,
    @ISBN INT,
    @TITLE VARCHAR(MAX),
    @AUTHOR VARCHAR(MAX),
    @PUBLISHER VARCHAR(MAX),
    @YEAR INT,
    @QUANTITY INT
AS
BEGIN
      INSERT INTO BOOKS (BOOK_ID, FACULTY_ID, DEPT_ID, ISBN, TITLE, AUTHOR, PUBLISHER, YEAR, QUANTITY)
      VALUES (@BOOK_ID, @FACULTY_ID, @DEPT_ID, @ISBN, @TITLE, @AUTHOR, @PUBLISHER, @YEAR, @QUANTITY);
END;

EXEC spAddNewBook @BOOK_ID = , @FACULTY_ID = , @DEPT_ID = , @ISBN = , @TITLE = , @AUTHOR =' ', @PUBLISHER = ' ', @YEAR = , @QUANTITY=

2. Without Parameters

This performs fixed tasks without requiring any user input. Example: Generating a student detail without input parameters.

GO
CREATE PROCEDURE spSTUDENTDETAILS
AS
BEGIN
    SELECT std_id, first_name,Last_name,Gender
    FROM STUDENT
END;

How to Find and Run Stored Procedures

In SQL Server, stored procedures are found in the Programmability folder within the database.

  1. Navigate to the database using the SQL Server Management Studio (SSMS) GUI.

  2. Expand the Programmability folder to locate the stored procedures.

Example: Automating University Library Management System Tasks

Let’s consider a library management system. One common task is to automatically update the status of borrowed books when a book is returned. Here’s how a stored procedure might handle this:

GO
CREATE PROCEDURE Register_borrower
                (   
                @Bor_id int,
                @dept_id int,
                @BOOK_ID int,
                @DateBor datetime,
                @returndate DATETIME,
                @borrow_status varchar(50)
                )
AS 
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION

                    --CHECK ELIGIBILTY OF BOOK
                    IF(SELECT COUNT(BOR_ID) FROM BORROWER WHERE BOR_ID = @BOR_ID AND RETURN_DATE IS NULL) > 3
                        BEGIN 
                            RAISERROR('MAXIMUM BOOK REACHED, YOU CAN''T BORROW',16,1)
                            RETURN
                        END
            ROLLBACK TRANSACTION

                --UPDATING THE BOOKS TABLE
                UPDATE BOOKS
                SET QUANTITY = QUANTITY - 1
                WHERE BOOK_ID = @BOOK_ID;

        ---INSERTING INTO THE BORROWER TABLE
        INSERT INTO BORROWER (BOR_ID,DEPT_ID,DATE_BORROWED,RETURN_DATE,BORROW_STATUS)
        VALUES(@BOR_ID,@DEPT_ID,@DATEBOR,@RETURNDATE,@BORROW_STATUS)
            COMMIT TRANSACTION
        END TRY
            BEGIN CATCH
                ROLLBACK TRANSACTION
                DECLARE  @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
                RAISERROR (@ErrorMessage, 16, 1);
            END CATCH
    END

To execute a stored procedure, use the EXECUTE command in SQL. For example:

EXEC Registerborrower 
    @Bor_id = 200, 
    @dept_id = 5, 
    @BOOK_ID = 12, 
    @DateBor = '2024-12-16 10:00:00', 
    @returndate = '2025-01-16 10:00:00', 
    @borrow_status = 'Borrowed';

Conclusion

Stored procedures are a powerful feature of SQL databases. For data enthusiasts, learning how to create and use stored procedures can transform the way you work with databases, making your processes faster and more efficient.

SQLdatabasemanagementstored procedure