Lesson 9: Stored Procedures and Functions

In this lesson, we will learn about stored procedures and functions in SQL. These are powerful tools that allow you to encapsulate complex logic and reuse SQL code efficiently.

What are Stored Procedures?

A stored procedure is a set of SQL statements that can be stored in the database and executed as a single unit. They can accept parameters, perform operations, and return results. Stored procedures help in: - Reusability of code - Improved performance - Enhanced security through encapsulation

Creating a Stored Procedure

To create a stored procedure, you use the CREATE PROCEDURE statement. Here’s an example:

CREATE PROCEDURE GetEmployeeByID(IN emp_id INT)
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = emp_id;
END;

Executing a Stored Procedure

To execute a stored procedure, you use the CALL statement:

CALL GetEmployeeByID(1);

What are Functions?

A function in SQL is similar to a stored procedure but is specifically designed to return a single value. Functions can be used in SQL expressions and can also accept parameters.

Creating a Function

To create a function, you use the CREATE FUNCTION statement. Here’s an example:

CREATE FUNCTION GetEmployeeName(emp_id INT) RETURNS VARCHAR(100)
BEGIN
    DECLARE emp_name VARCHAR(100);
    SELECT Name INTO emp_name FROM Employees WHERE EmployeeID = emp_id;
    RETURN emp_name;
END;

Using a Function

You can use a function in a query like this:

SELECT GetEmployeeName(1) AS EmployeeName;

Best Practices for Stored Procedures and Functions

• Keep it Simple: Avoid complex logic in stored procedures and functions. Break them down into smaller procedures if necessary.
• Use Meaningful Names: Name your procedures and functions descriptively to indicate their purpose.
• Error Handling: Implement error handling within your procedures and functions to manage exceptions gracefully.
• Avoid Side Effects: Functions should not change the state of the database; they should only return values.

Common Mistakes: - Forgetting to declare parameters in stored procedures and functions. - Using a function in a context that is not allowed (e.g., in a WHERE clause without returning a scalar value).

Summary

Stored procedures and functions are essential tools in SQL for encapsulating logic and improving code reusability. Understanding how to create and use them effectively is crucial for advanced SQL programming.

Exercise 1: Create a Stored Procedure

Create a stored procedure named GetDepartmentEmployees that takes a department ID as a parameter and returns all employees in that department.

Exercise 2: Create a Function

Create a function named CalculateAnnualSalary that takes an employee ID as a parameter and returns the annual salary of that employee (monthly salary * 12).

Exercise 3: Use Your Functions and Procedures

Use the stored procedure and function you created in the previous exercises in a SQL query to display the names of employees along with their annual salaries.

• Stored procedures encapsulate SQL statements and improve code reusability.
• Functions return a single value and can be used in SQL expressions.
• Best practices include keeping code simple, using meaningful names, and implementing error handling.
• Avoid side effects in functions to maintain their integrity.