Function vs procedure decision in SQL - Visual Side-by-Side Comparison

Concept Flow - Function vs procedure decision

Start: Need to perform a task

↓

Is a return value needed?

Yes→Use Function

No↓

Is it a complex operation or multiple steps?

Yes→Use Procedure

No↓

Use Procedure

Decide between function and procedure by checking if a return value is needed and the complexity of the task.

Execution Sample

SQL

CREATE FUNCTION get_discount(price DECIMAL) RETURNS DECIMAL
BEGIN
  RETURN price * 0.1;
END;

CREATE PROCEDURE apply_discount(IN price DECIMAL, OUT final_price DECIMAL)
BEGIN
  SET final_price = price - (price * 0.1);
END;

Defines a function that returns a discount value and a procedure that applies the discount and outputs the final price.

Execution Table

Step	Decision Point	Condition	Action Taken	Result
1	Need to perform a task	N/A	Check if return value needed	Proceed to next step
2	Is a return value needed?	Yes	Choose Function	Function returns a value
3	Is a return value needed?	No	Check if complex or multiple steps	Proceed to next step
4	Is it complex or multiple steps?	Yes	Choose Procedure	Procedure performs actions without return
5	Is it complex or multiple steps?	No	Choose Procedure	Procedure performs simple action without return
6	End	N/A	Decision made	Function or Procedure chosen

💡 Decision ends when function or procedure is chosen based on return value need and complexity

Variable Tracker

Variable	Start	After Step 2	After Step 4	Final
ReturnValueNeeded	Unknown	Yes or No	N/A	Determines Function or Procedure
Complexity	Unknown	N/A	Yes or No	Determines Procedure or simple Procedure

Key Moments - 3 Insights

Why do we choose a function when a return value is needed?

Because functions are designed to return a single value, as shown in execution_table row 2 where the condition 'Yes' leads to choosing a function.

Can procedures return values like functions?

Procedures do not return values directly but can use output parameters; this is why when no return value is needed or multiple steps are involved, procedures are chosen (rows 3 and 4).

What if the task is simple but no return value is needed?

Even for simple tasks without return values, procedures are used as per execution_table row 5.

Visual Quiz - 3 Questions

Look at the execution_table, at which step do we decide to use a function?

A Step 5

B Step 4

C Step 2

D Step 6

💡 Hint

Check the 'Action Taken' column for the step where 'Choose Function' appears.

According to variable_tracker, what variable influences choosing a procedure for complex tasks?

A Complexity

B ReturnValueNeeded

C TaskType

D OutputParameter

💡 Hint

Look at the variable that changes after Step 4 indicating complexity.

If the task needs no return value and is simple, what does the execution_table say to choose?

A Function

B Procedure

C Neither

D Both

💡 Hint

Refer to Step 5 in the execution_table under 'Action Taken'.

Concept Snapshot

Function vs Procedure Decision:
- Use Function when a return value is needed.
- Use Procedure for complex tasks or multiple steps.
- Procedures can have output parameters but do not return values directly.
- Functions return a single value and are used in expressions.
- Choose based on task complexity and return value need.

Full Transcript

This visual execution shows how to decide between using a function or a procedure in SQL. First, check if the task requires a return value. If yes, use a function because functions return values directly. If no return value is needed, check if the task is complex or involves multiple steps. For complex or multi-step tasks, use a procedure. Procedures perform actions and can use output parameters but do not return values like functions. For simple tasks without return values, procedures are also used. The execution table traces these decisions step-by-step, and the variable tracker shows how the decision variables change. Key moments clarify common confusions about return values and task complexity. The quiz tests understanding of when to choose functions or procedures based on the visual trace.

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