Function vs procedure decision in SQL - Hands-On Comparison

Function vs Procedure Decision in SQL

📖 Scenario: You work at a small company that manages employee data. You need to create reusable database code to calculate employee bonuses and to update employee records.

🎯 Goal: Build a simple SQL function to calculate a bonus based on salary and a procedure to update an employee's department.

📋 What You'll Learn

Create a function named calculate_bonus that takes salary as input and returns 10% of it.

Create a procedure named update_department that takes employee_id and new_department as inputs and updates the employee's department.

Use the function inside the procedure to calculate the bonus for the employee.

Ensure the function returns a value and the procedure performs an update without returning a value.

💡 Why This Matters

🌍 Real World

Companies often use functions to calculate values like bonuses or taxes and procedures to update employee records or perform complex operations.

💼 Career

Understanding when to use functions versus procedures is important for database developers and administrators to write efficient and maintainable SQL code.

1

Create the calculate_bonus function

Write a SQL function named calculate_bonus that takes one parameter salary of type DECIMAL and returns 10% of the salary as a DECIMAL.

SQL

-- Write the SQL code to create the function calculate_bonus
-- Your code here

Need a hint?

Functions return a value and can be used in queries. Use RETURN to send back the result.

2

Create the update_department procedure

Write a SQL procedure named update_department that takes two parameters: employee_id as INT and new_department as VARCHAR(50). The procedure should update the department field in the employees table for the given employee_id.

SQL

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

-- Write the SQL code to create the procedure update_department
-- Your code here

Need a hint?

Procedures perform actions but do not return values. Use UPDATE to change data.

3

Use the function inside the procedure

Modify the update_department procedure to declare a variable bonus of type DECIMAL. Use the calculate_bonus function to set bonus based on the employee's current salary from the employees table. Do not change the update statement.

SQL

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

CREATE PROCEDURE update_department(employee_id INT, new_department VARCHAR(50))
BEGIN
    -- Declare bonus variable
    -- Your code here
    UPDATE employees SET department = new_department WHERE id = employee_id;
END;

Need a hint?

Use DECLARE to create a variable inside the procedure. Use SELECT ... INTO to assign the function result.

4

Complete the procedure with a comment

Add a comment inside the update_department procedure after the update statement that says -- Department updated and bonus calculated.

SQL

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

CREATE PROCEDURE update_department(employee_id INT, new_department VARCHAR(50))
BEGIN
    DECLARE bonus DECIMAL;
    SELECT calculate_bonus(salary) INTO bonus FROM employees WHERE id = employee_id;
    UPDATE employees SET department = new_department WHERE id = employee_id;
    -- Your comment here
END;

Need a hint?

Comments help explain what the code does. Use -- for single-line comments in SQL.