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.