Home » Courses » SQL Crash Course

SQL Stored Procedures and Functions – Reusable SQL Logic

Lesson 16: SQL Stored Procedures and Functions – Reusable SQL Logic

Estimated Read Time: 25-30 minutes

Objective: In this lesson, we will learn about SQL Stored Procedures and SQL Functions. Both of these allow you to group multiple SQL statements into reusable blocks of code. Stored procedures and functions improve code reusability, simplify complex queries, and help in maintaining data integrity.

1. What are Stored Procedures?

A stored procedure is a collection of one or more SQL statements that are grouped together and stored in the database. You can execute these stored procedures whenever you need to run the same logic multiple times, instead of writing the SQL code again and again.

Stored procedures can be used for a variety of tasks, such as:

Data validation
Inserting, updating, or deleting data
Performing calculations or business logic
Managing user permissions

2. Syntax for Creating a Stored Procedure

The syntax for creating a stored procedure is as follows:

CREATE PROCEDURE procedure_name
AS
BEGIN
    -- SQL statements go here
END;

Example: Let’s create a stored procedure to update an employee’s salary in the employees table:

CREATE PROCEDURE update_salary (IN emp_id INT, IN new_salary DECIMAL)
BEGIN
    UPDATE employees
    SET salary = new_salary
    WHERE employee_id = emp_id;
END;

In the above example:

emp_id and new_salary are the parameters for the procedure.
employee_id is the column in the employees table that will be updated with the new salary.

You can call the procedure like this:

CALL update_salary(101, 55000);

This will update the salary of the employee with ID 101 to 55,000.

3. What are SQL Functions?

An SQL function is similar to a stored procedure, but it returns a value. Functions are often used for calculations, string manipulations, or data transformations. They can be called in SQL statements just like other SQL expressions.

For example, you can create a function to calculate the annual salary from a monthly salary:

CREATE FUNCTION calculate_annual_salary (monthly_salary DECIMAL)
RETURNS DECIMAL
BEGIN
    RETURN monthly_salary * 12;
END;

Now, you can use this function in a SELECT query to calculate the annual salary of employees:

SELECT employee_id, calculate_annual_salary(salary) AS annual_salary
FROM employees;

4. Difference Between Stored Procedures and Functions

While both stored procedures and functions are used to encapsulate logic, they differ in some key ways:

Feature	Stored Procedure	Function
Return Value	Does not return a value (can return multiple result sets)	Returns a single value
Callability	Called using `CALL` statement	Used directly in SQL expressions
Use in SELECT	Cannot be used directly in SELECT queries	Can be used in SELECT queries
Side Effects	Can modify data or perform actions	Typically used for calculations or transformations

5. Example 1: Stored Procedure for Inserting Data

Let’s create a stored procedure that inserts a new employee into the employees table:

CREATE PROCEDURE insert_employee (IN emp_name VARCHAR(100), IN emp_salary DECIMAL)
BEGIN
    INSERT INTO employees (name, salary)
    VALUES (emp_name, emp_salary);
END;

This stored procedure takes the employee name and salary as input parameters and inserts a new record into the employees table.

6. Example 2: Function for Calculating Tax

Let’s create a function to calculate the tax based on the salary. Assume a tax rate of 10%:

CREATE FUNCTION calculate_tax (salary DECIMAL)
RETURNS DECIMAL
BEGIN
    RETURN salary * 0.10;
END;

You can use this function in a SELECT statement to calculate the tax for all employees:

SELECT employee_id, calculate_tax(salary) AS tax
FROM employees;

7. Example 3: Using a Stored Procedure with Multiple SQL Statements

In some cases, you might want to run multiple SQL statements inside a stored procedure. For example, let’s create a stored procedure that both updates the salary and logs the update in an audit table:

CREATE PROCEDURE update_salary_and_log (IN emp_id INT, IN new_salary DECIMAL)
BEGIN
    -- Update salary
    UPDATE employees
    SET salary = new_salary
    WHERE employee_id = emp_id;

    -- Log the update in the audit table
    INSERT INTO salary_audit (employee_id, new_salary, change_date)
    VALUES (emp_id, new_salary, NOW());
END;

8. Calling and Modifying Stored Procedures and Functions

To call a stored procedure, use the CALL statement:

CALL procedure_name(arguments);

To call a function, you can use it in a SELECT statement or as part of any SQL expression:

SELECT function_name(arguments);

If you need to modify an existing stored procedure or function, you’ll need to drop and recreate it, as SQL doesn’t support directly altering them. Use the DROP PROCEDURE or DROP FUNCTION commands to remove the existing objects:

DROP PROCEDURE procedure_name;
DROP FUNCTION function_name;

9. Best Practices

When using stored procedures and functions, it is important to follow best practices to ensure that your code is efficient and maintainable:

Keep stored procedures focused on a single task or operation to improve readability and reusability.
Use meaningful names for your stored procedures and functions to describe their purpose.
Test stored procedures and functions thoroughly to ensure they work as expected.
Consider the performance impact of your stored procedures, especially when dealing with large datasets.

10. Conclusion

In this lesson, we learned about SQL stored procedures and functions. These are powerful tools for encapsulating reusable logic, improving code efficiency, and simplifying database operations. Stored procedures allow for more complex logic with multiple SQL statements, while functions are ideal for performing calculations or transformations.

We covered:

What stored procedures and functions are and how they work.
The syntax for creating and calling stored procedures and functions.
Examples of stored procedures for inserting data and logging updates, and functions for calculations.
Best practices for using stored procedures and functions in SQL.

In the next lesson, we will explore SQL Constraints for ensuring data integrity and enforcing business rules at the database level.

SQL Crash Course