What are Stored Procedures and Why Should You Use Them?

The DRY (Don't Repeat Yourself) principle in software development means avoiding repeated code or logic. It says that each piece of information or logic should exist in only one place in a system. 

The same idea can be applied to database operations using stored procedures. 

What is a Stored Procedure? 

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. 

So, if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. 

It is very useful as it reduces the need for SQL queries. 

It enhances efficiency, reusability, and security in database management.  

Stored procedure between client and a database

 This image illustrates how a stored procedure works between a client and a database. Let’s break it down step-by-step: 

Key Components: 

1. Client:   

    This is where a user or an application interacts with the system. It sends SQL queries (commands) to request or modify data. 

2. Stored Procedure:   

    A stored procedure is a group of SQL queries that are stored in the database and can be executed as a single unit. It can perform actions like Read, Insert, Update, and Delete data. 

3. Database: 

   This is where the data is stored and managed. 

How it works: 

1. The client sends SQL queries to the database to perform some operations (like getting or updating data).   

2. Instead of sending multiple queries, the client can call a stored procedure.   

   - The stored procedure already contains the SQL commands inside it.   

   - It helps in reducing the back-and-forth communication between the client and the database. 

3. The stored procedure interacts with the database to fetch or update the required data.   

4. The database sends the data back to the client. 

What is the Purpose of using a procedure? 

The purpose of using a stored procedure in SQL is to simplify and optimize database operations. Lets get into details. 

1. Giving More Power to SQL Language 

SQL itself is a very powerful language for querying and managing relational databases. However, SQL is primarily designed to retrieve, insert, update, and delete data.  

Procedures extend the power of SQL by allowing procedural programming features such as: 

• Conditional logic (e.g., IF-ELSE statements) 

• Loops (e.g., WHILE or FOR loops) 

• Error handling 

• Variables and parameterized inputs 

• Complex workflows combining multiple queries 

  
  
  
  

This makes procedures act like user-defined functions but with more control and flexibility.  

2. When SQL Queries Alone Aren’t Enough 

Sometimes, there are tasks or requirements that cannot be achieved with simple SQL queries. A procedure is introduced to fill this gap. It allows combining multiple queries, applying logic, and orchestrating complex tasks. 

Here are scenarios where stored procedures excel: 

A.SQL alone cannot perform complex conditional checks:

Procedures allow us to add decision-making logic. 

Example: 

A stored procedure can be used to increase an employee's salary only if their performance rating meets a certain level. If the rating is too low, no salary increase will occur. 

CREATE PROCEDURE UpdateSalary   

    @EmpID INT,   

    @Rating INT,   

    @NewSalary DECIMAL   

AS   

BEGIN   

    IF @Rating >= 4   

        UPDATE Employees SET Salary = @NewSalary WHERE EmployeeID = @EmpID;   

    ELSE   

        PRINT 'Salary cannot be updated due to low performance rating.';   

END;   

Without a procedure, this would require application-level logic, increasing complexity.  

B.Looping or Repeated Operations 

Standard SQL doesn’t support looping over records directly. Procedures can implement loops (e.g., WHILE) to perform repeated operations. 

Example: A procedure to update bonuses for all employees one by one. 

CREATE PROCEDURE UpdateAllBonuses   

AS   

BEGIN   

    DECLARE @EmpID INT;   

    DECLARE CursorEmp CURSOR FOR SELECT EmployeeID FROM Employees;   

    OPEN CursorEmp;     

    FETCH NEXT FROM CursorEmp INTO @EmpID;   

    WHILE @@FETCH_STATUS = 0   

    BEGIN   

        UPDATE Employees SET Bonus = Bonus + 1000 WHERE EmployeeID = @EmpID;   

        FETCH NEXT FROM CursorEmp INTO @EmpID;   

    END;   

    CLOSE CursorEmp;   

    DEALLOCATE CursorEmp;   

END;   

A cursor in SQL is like a pointer that allows you to work with rows in a query result one at a time, instead of processing all the rows at once. 

In the given code, the cursor is named CursorEmp. Its purpose is to: 

1.Retrieve all EmployeeID values from the Employees table. 

2.Process each EmployeeID one by one to update the Bonus column. 

Purpose of the Cursor: 

1.DECLARE Cursor: 

DECLARE CursorEmp CURSOR FOR SELECT EmployeeID FROM Employees; 

• This defines the cursor named CursorEmp. 

• The cursor will select all EmployeeID values from the Employees table. 

  
  

2. OPEN Cursor: 

OPEN CursorEmp; 

• Opens the cursor and prepares it to start fetching rows from the query result. 

3.FETCH NEXT: 

FETCH NEXT FROM CursorEmp INTO @EmpID;

• Moves the cursor to the next row in the result set and assigns the EmployeeID value to the variable @EmpID. 

4.WHILE @@FETCH_STATUS = 0:

• This loop runs as long as there are rows left to process.

• @@FETCH_STATUS is a system variable that indicates whether the last fetch operation was successful (0 means successful). 

 5. UPDATE Statement Inside the Loop:

UPDATE Employees SET Bonus = Bonus + 1000 WHERE EmployeeID = @EmpID;

• For each @EmpID fetched by the cursor, the procedure increases the Bonus of the corresponding employee by 1000.

6.CLOSE Cursor:

CLOSE CursorEmp;

• Closes the cursor once all rows have been processed.

7.DEALLOCATE Cursor:

DEALLOCATE CursorEmp;

• Releases the memory resources associated with the cursor.

  
  
  
  

  Why use a Cursor here?

  
  

  The cursor is used because the Bonus for each employee needs to be updated individually.

  • Instead of performing a bulk operation (e.g., updating all rows in one UPDATE query), the cursor processes each employee one by one.

  • This is helpful when the operation on each row is more complex or requires specific logic for each row.

C. Combining Multiple SQL Statements (Complex Workflows) 

Sometimes, a task requires executing multiple SQL queries in a specific sequence. Procedures allow you to combine queries into one logical unit. 

Example: A procedure to transfer money between two accounts. 

CREATE PROCEDURE TransferMoney

@FromAccount INT,

@ToAccount INT,

@Amount DECIMAL

AS

BEGIN

BEGIN TRANSACTION;

-- Deduct amount from sender

UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @FromAccount;

-- Add amount to receiver

UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ToAccount;

-- Check for errors

IF @@ERROR <> 0

ROLLBACK TRANSACTION;

ELSE

COMMIT TRANSACTION;

END;

This workflow is tough to manage in a single SQL query.

D. Error Handling 

SQL queries alone can't handle errors very well. Stored procedures can catch errors and take actions like undoing changes (rollbacks) or keeping records of the error (logs). 

Example: If an error occurs during an operation, rollback the transaction. 

CREATE PROCEDURE SafeInsert

AS

BEGIN

BEGIN TRY

BEGIN TRANSACTION;

INSERT INTO Products (Name, Price) VALUES ('Book', 25);

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION;

PRINT 'An error occurred while inserting data.';

END CATCH;

END; 

 

E. Reusable Logic

When a query or task needs to be executed repeatedly, a stored procedure centralizes the logic, ensuring consistency and reducing duplication. 

Example: Fetching data for reports that are required daily. 

CREATE PROCEDURE DailySalesReport

AS

BEGIN

SELECT ProductID, SUM(Quantity) AS TotalSold

FROM Sales

WHERE SaleDate = CAST(GETDATE() AS DATE)

GROUP BY ProductID;

END;

F. Security and Access Control 

• With procedures, you can restrict direct access to tables. 

• Users only need permission to execute the procedure, not to access sensitive tables. 

• The database administrator can hide the underlying logic from users. 

  
  

Why Procedures Are Like User-Defined Functions 

• Procedures allow encapsulation of logic, just like a function. 

• They take inputs (parameters), execute logic, and return results. 

• The difference is that procedures are more powerful because: 

• They can perform multiple actions (Insert, Update, Delete, Select). 

• They allow transactions, error handling, and control flows (IF, WHILE). 

  
  

Stored procedures are introduced to extend SQL functionality and address requirements that SQL queries alone cannot fulfill, such as Conditional logic, Loops, Complex workflows, Error handling, Security, Code reusability. 

They act as a bridge between simple SQL queries and advanced programming needs, giving developers more control and flexibility over database operations. 

Syntax 

MS SQL Syntax 

CREATE PROCEDURE procedure_name AS 

 sql_statement 

GO;  

 

PostgreSQL Syntax 

PostgreSQL Stored Procedure Syntax 

CREATE OR REPLACE PROCEDURE procedure_name(parameter_name datatype) 

LANGUAGE plpgsql 

AS $$ 

DECLARE 

    -- variable declaration 

BEGIN 

    -- PROCEDURE body – all logic 

END;  

$$;  

1. CREATE OR REPLACE PROCEDURE procedure_name(parameter_name datatype): 

• CREATE OR REPLACE PROCEDURE: This part declares that we're creating or replacing a stored procedure named procedure_name. If a procedure with the same name already exists, it will be replaced with the new definition. 

• parameter_name datatype: This defines the input parameter for the procedure. You can have multiple parameters, each with its own name and data type. 

  
  

2. LANGUAGE plpgsql: 

• This specifies that the procedure will be written in the PL/pgSQL language, which is a procedural language for PostgreSQL. 

  
  

3. AS $$: 

• This marks the beginning of the procedure's code block. 

  
  

4. DECLARE: 

• This section is optional and is used to declare variables that will be used within the procedure. You can declare variables of different data types (e.g., integer, string, date). 

5. BEGIN: 

• This marks the start of the procedure's executable code. 

  
  

6. -- PROCEDURE body – all logic: 

• This is where you write the actual logic of the procedure. It can include SQL statements, control flow constructs (e.g., IF, ELSE, LOOP), and function calls. 

  
  

7. END; :This marks the end of the procedure. 

Example 

CREATE OR REPLACE PROCEDURE UpdateEmployeeSalary(emp_id INT, new_salary NUMERIC) 

LANGUAGE plpgsql 

AS $$ 

BEGIN 

    UPDATE Employees 

    SET Salary = new_salary 

    WHERE EmployeeID = emp_id;   

 END; 

$$; 

 

Calling a Stored Procedure 

To call a stored procedure: 

• Use CALL followed by the procedure name. 

• Provide arguments in parentheses if the procedure expects parameters. 

CALL UpdateEmployeeSalary(101, 75000); 

Why Use CALL to Execute Procedures? 

• CALL makes it clear that you're invoking a stored procedure. 

• It allows you to execute complex database logic in a single step. 

• This is standard SQL syntax supported by most relational databases like PostgreSQL, MySQL, and Oracle. 

Advantages of using Stored Procedures

1. Reusability

• Once a stored procedure is created, you can use it multiple times without writing the same code again.

• Example: If you need to calculate discounts often, you can create a procedure and just call it whenever needed.

  
  

2. Faster Performance

• Stored procedures are pre-compiled, so the database doesn't need to process them from scratch every time.

• This makes them faster compared to running SQL queries directly.

  
  

3. Easier Maintenance

• If there’s a change in logic, you only need to update the stored procedure in one place instead of updating multiple SQL queries across your application.

  
  

4. Improved Security

• Users can execute a stored procedure without knowing the actual SQL code behind it.

• You can also control access to sensitive data by granting permissions only to procedures, not tables.

  
  

5. Error Handling

• You can write code inside stored procedures to catch and manage errors (e.g., log errors or rollback changes) instead of letting the entire application crash.

  
  

6. DRY Principle

• They follow the Don't Repeat Yourself principle, ensuring that logic is centralized and not repeated in multiple places.

  
  

7. Reduced Network Traffic

• Instead of sending multiple SQL queries over the network, you send a single request to call the procedure, which reduces network load.

  
  

8. Modularity

• Procedures break down complex operations into smaller, reusable blocks of code, making them easier to understand and manage.

  
  

Stored procedures save you time, improve speed, increase security, and make your database operations cleaner and more efficient. They are like ready-made recipes in cooking: once created, you just follow the steps to get consistent results.

Disadvantages of using stored procedures

1. Harder to Debug

• Troubleshooting stored procedures can be tricky since they run inside the database, and debugging tools are often less user-friendly than those for application code.

  
  

2. Limited Portability

• Stored procedures are written in the specific syntax of a database (ex: MS SQL, PostgreSQL).

• If you switch to a different database, you may need to rewrite them.

  
  

3. Increased Complexity

• Writing and maintaining stored procedures requires specialized knowledge of the database’s procedural language, making it more complex for beginners.

  
  

4. Harder to Version Control

• Unlike application code, it’s not as straightforward to track changes or maintain versions of stored procedures in source control systems like Git.

  
  

5. Performance

• If too much business logic is moved to stored procedures, it can overload the database server, affecting overall performance.

  
  

6. Dependency on Database Administrators (DBAs)

• Changes to stored procedures often require DBA involvement, which can slow down development and updates.

  
  

7. Reduced Flexibility

• Application code is easier to modify and test compared to stored procedures. Making frequent changes to stored procedures can disrupt the database.

  
  

While stored procedures are powerful, they can make debugging, switching databases, and maintenance harder. If overused, they can slow down your system and make changes more complicated. It’s important to use them wisely and balance their benefits with these drawbacks.

Hope this information helped you in your journey to mastering stored procedures! Remember, coding is like cooking—mix the right ingredients (logic and syntax), and you’ll create magic in your database.

As they say, 'Keep calm and let the procedures do the talking!' Happy coding!