Automating Workflows with Stored Procedures

Welcome to the final day of Week 2 Day 5 in our Database Decoded series! Today, we’ll unlock the power of stored procedures—reusable SQL scripts that automate complex tasks, enhance security, and streamline your workflow. Let’s wrap up the week by learning to write efficient, dynamic, and secure database operations.

Why Stored Procedures Matter

Stored procedures are like pre-written recipes for your database. They allow you to:

• Reuse code: Avoid rewriting frequent queries (e.g., monthly reports).

• Improve security: Restrict direct table access by granting users permission to run procedures only.

• Boost performance: Pre-compiled execution plans make them faster than ad-hoc queries.

Real-World Use Cases:

• Generating payroll reports.

• Bulk-inserting data from external sources.

• Enforcing business rules (e.g., validating discounts).

Topics Covered

1. Creating Basic Stored Procedures

A stored procedure groups SQL statements into a single executable unit.

Example: Fetch Employees by Department

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

• Execute the Procedure:

  EXEC GetEmployeesByDepartment @DeptID = 2; -- Returns IT employees  

2. Using Parameters for Dynamic Results

Parameters make procedures flexible. You can pass values at runtime.

Example: Insert New Employees Securely

CREATE PROCEDURE AddEmployee  
    @EmpID INT,  
    @FirstName VARCHAR(50),  
    @LastName VARCHAR(50),  
    @Salary DECIMAL(10,2)  
AS  
BEGIN  
    INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary)  
    VALUES (@EmpID, @FirstName, @LastName, @Salary);  
END  

• Execute with Parameters:

  EXEC AddEmployee @EmpID=5, @FirstName='Lisa', @LastName='Kim', @Salary=72000.00;  

3. Securing Your Database

Limit direct table access by granting users permission to execute procedures only.

Step 1: Create a Role

CREATE ROLE ReportViewer;  

Step 2: Grant Execute Permission

GRANT EXECUTE ON GetEmployeesByDepartment TO ReportViewer;  

Result: Users in ReportViewer can run the procedure but can’t directly query or modify the Employees table.

Practice Tasks

Task 1: Create a Salary Update Procedure

Write a stored procedure to give raises to employees in a specific department:

-- Example: 10% raise for DeptID = 3 (Finance)  
EXEC GiveRaise @DeptID=3, @RaisePercentage=10;  

Task 2: Experiment with RIGHT JOIN

1. Create a Projects table linked to Employees via EmployeeID.

2. Write a stored procedure using RIGHT JOIN to list all projects, including those unassigned to employees.

Procedure Template:

CREATE PROCEDURE GetProjectAssignments  
AS  
BEGIN  
    SELECT P.ProjectName, E.FirstName  
    FROM Projects P  
    RIGHT JOIN Employees E ON P.EmployeeID = E.EmployeeID;  
END  

Task 3: Secure Your Procedure

Create a role HR_Manager and grant it permission to execute the AddEmployee procedure.

Key Takeaways

• Stored Procedures: Reusable, parameter-driven SQL scripts for automation.

• Security: Restrict direct table access by granting execute permissions.

• Dynamic Queries: Use parameters to make procedures flexible.

• JOINs: Combine RIGHT JOIN with procedures for advanced reporting.