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

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

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;

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;

Hint

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

Practice

1. Which of the following is true about SQL functions compared to procedures?

easy

A. Functions return a value and can be used in SELECT queries.

B. Functions perform actions but do not return any value.

C. Procedures always return a value and can be used in WHERE clauses.

D. Procedures can be used inside SELECT statements like functions.

Solution

1. Step 1: Understand the purpose of functions

   Functions are designed to return a value and can be called within queries like SELECT.

2. Step 2: Compare with procedures

   Procedures perform tasks but do not return values directly and cannot be used inside SELECT statements.

3. Final Answer:

   Functions return a value and can be used in SELECT queries. -> Option A

4. Quick Check:

   Functions return values [OK]

Hint: Functions return values; procedures perform tasks [OK]

Common Mistakes:

• Thinking procedures return values like functions
• Using procedures inside SELECT queries
• Confusing function usage with procedure usage

2. Which of the following is the correct syntax to create a SQL function that returns an integer?

easy

A. CREATE PROCEDURE getCount() BEGIN RETURN 10; END;

B. CREATE PROCEDURE getCount() RETURNS INT BEGIN RETURN 10; END;

C. CREATE FUNCTION getCount() BEGIN RETURN 10; END;

D. CREATE FUNCTION getCount() RETURNS INT BEGIN RETURN 10; END;

Solution

1. Step 1: Identify function syntax

   Functions require specifying RETURNS type and a RETURN statement inside BEGIN...END.

2. Step 2: Check options for correctness

   CREATE FUNCTION getCount() RETURNS INT BEGIN RETURN 10; END; correctly declares a function with RETURNS INT and returns a value. Others either use PROCEDURE or miss RETURNS.

3. Final Answer:

   CREATE FUNCTION getCount() RETURNS INT BEGIN RETURN 10; END; -> Option D

4. Quick Check:

   Functions need RETURNS and RETURN [OK]

Hint: Functions need RETURNS and RETURN statements [OK]

Common Mistakes:

• Using PROCEDURE keyword for functions
• Omitting RETURNS clause in function
• Using RETURN in procedures incorrectly

3. Given the following SQL code, what will be the output of SELECT calculateTax(100); if calculateTax is a function that returns 10% of the input amount?

CREATE FUNCTION calculateTax(amount INT) RETURNS INT BEGIN RETURN amount * 0.1; END;

medium

A. 100

B. 0.1

C. 10

D. Error: Cannot multiply INT by decimal

Solution

1. Step 1: Understand function logic

   The function multiplies the input amount by 0.1 to calculate 10% tax.

2. Step 2: Calculate output for input 100

   100 * 0.1 = 10, so the function returns 10.

3. Final Answer:

   10 -> Option C

4. Quick Check:

   100 * 0.1 = 10 [OK]

Hint: Multiply input by 0.1 to get 10% tax [OK]

Common Mistakes:

• Confusing 0.1 with 10
• Expecting decimal output when function returns INT
• Assuming error due to type mismatch

4. You wrote this procedure to update a user's email:

CREATE PROCEDURE updateEmail(userId INT, newEmail VARCHAR(100)) BEGIN UPDATE users SET email = newEmail WHERE id = userId; END;

When you try to call it with SELECT updateEmail(1, '[e-mail]');, you get an error. What is the problem?

medium

A. Procedures cannot be called inside SELECT statements.

B. The procedure is missing a RETURN statement.

C. The UPDATE statement syntax is incorrect.

D. The procedure parameters must be declared as OUT.

Solution

1. Step 1: Understand procedure usage

   Procedures perform actions but do not return values and cannot be called inside SELECT queries.

2. Step 2: Identify error cause

   Calling a procedure with SELECT causes error because procedures are invoked with CALL, not SELECT.

3. Final Answer:

   Procedures cannot be called inside SELECT statements. -> Option A

4. Quick Check:

   Use CALL for procedures, not SELECT [OK]

Hint: Call procedures with CALL, not SELECT [OK]

Common Mistakes:

• Trying to use SELECT to call procedures
• Expecting procedures to return values
• Misunderstanding parameter directions

5. You need to create a reusable SQL routine that calculates the discount price and also logs the discount application in a table. Which approach is best?

hard

A. Use a single function to calculate discount and log the discount inside it.

B. Use a function to calculate discount and a procedure to log the discount.

C. Use a procedure to calculate discount and return the discounted price.

D. Use a procedure only to calculate discount and log the discount.

Solution

1. Step 1: Separate calculation and action

   Functions should be used for calculations returning values; procedures for actions like logging.

2. Step 2: Apply best practice

   Use a function to return the discount price, and a procedure to perform the logging task separately.

3. Final Answer:

   Use a function to calculate discount and a procedure to log the discount. -> Option B

4. Quick Check:

   Functions calculate; procedures perform tasks [OK]

Hint: Calculate with functions; log with procedures [OK]

Common Mistakes:

• Trying to do logging inside functions
• Expecting procedures to return values
• Mixing calculation and side effects in one routine