User-defined functions in SQL - Step-by-Step Execution

Concept Flow - User-defined functions

Define function with name and parameters

↓

Write function body with SQL statements

↓

Save function in database

↓

Call function in SQL query

↓

Function executes and returns result

↓

Result used in query output

This flow shows how you create a function, save it, then call it in a query to get results.

Execution Sample

SQL

CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

SELECT add_numbers(3, 5);

Defines a function that adds two numbers and then calls it with 3 and 5.

Execution Table

Step	Action	Input Parameters	Operation	Output/Result
1	Define function add_numbers	a, b	Store function logic: RETURN a + b	Function saved in database
2	Call function add_numbers	3, 5	Calculate 3 + 5	8
3	Return result	N/A	Output value from function	8

💡 Function call completes and returns the sum of inputs.

Variable Tracker

Variable	Start	After Call	Final
a	undefined	3	3
b	undefined	5	5
return_value	undefined	8	8

Key Moments - 3 Insights

Why do we need to define the function before calling it?

The function must be saved in the database first (see execution_table step 1) so SQL knows what code to run when called.

What happens if we call the function with different numbers?

The function uses the new input values for a and b and returns their sum (like step 2), so output changes accordingly.

Why is the return value important?

The return value is what the function outputs to the query (step 3), so it must be correctly calculated inside the function.

Visual Quiz - 3 Questions

Look at the execution_table, what is the output when add_numbers is called with inputs 3 and 5?

A 15

B 8

C 35

D Error

💡 Hint

Check execution_table row 2 where inputs 3 and 5 are added.

At which step is the function logic stored in the database?

A Step 1

B Step 2

C Step 3

D After the query

💡 Hint

Look at execution_table row 1 describing function definition.

If we call add_numbers(10, 20), what will be the return_value in variable_tracker after call?

A 10

B 20

C 30

D Undefined

💡 Hint

Return value is sum of inputs, see variable_tracker return_value after call.

Concept Snapshot

CREATE FUNCTION name(params) RETURNS type AS $$ BEGIN ... END; $$ LANGUAGE plpgsql;
Call with SELECT name(args);
Function runs code with inputs,
returns a value used in queries.
Must define before calling.

Full Transcript

User-defined functions in SQL let you create reusable code blocks that take inputs and return outputs. First, you define the function with a name, parameters, and a body containing SQL logic. This definition is saved in the database. Then, you call the function in a query with specific arguments. The function executes its code using these inputs and returns a result. For example, a function add_numbers(a, b) returns the sum of a and b. When called with 3 and 5, it returns 8. Variables inside the function hold input values and the return value. Defining the function first is essential so SQL knows what to run. Changing inputs changes the output accordingly. The return value is what the query receives from the function call.

Practice

1. What is the main purpose of a user-defined function in SQL?

easy

A. To create reusable code that returns a single value

B. To store large amounts of data

C. To create tables automatically

D. To delete records from a table

Solution

1. Step 1: Understand what user-defined functions do

   User-defined functions are designed to perform specific tasks and return a single value.

2. Step 2: Compare options with the function's purpose

   Only To create reusable code that returns a single value describes creating reusable code that returns a single value, which matches the function's role.

3. Final Answer:

   To create reusable code that returns a single value -> Option A

4. Quick Check:

   User-defined function = reusable single-value code [OK]

Hint: Functions return one value and reuse code easily [OK]

Common Mistakes:

• Confusing functions with tables or data storage
• Thinking functions delete or modify data directly
• Assuming functions return multiple rows or tables

2. Which of the following is the correct way to start creating a scalar user-defined function in SQL?

easy

A. DROP FUNCTION function_name();

B. CREATE FUNCTION function_name() RETURNS INT AS BEGIN RETURN 1; END;

C. SELECT FUNCTION function_name();

D. CREATE TABLE function_name();

Solution

1. Step 1: Identify the correct syntax for creating a function

   The correct syntax starts with CREATE FUNCTION, specifies the return type, and includes a BEGIN...END block.

2. Step 2: Check each option's validity

   CREATE FUNCTION function_name() RETURNS INT AS BEGIN RETURN 1; END; matches the correct syntax. Options A, B, and C are for other SQL commands (dropping, table creation, and selection).

3. Final Answer:

   CREATE FUNCTION function_name() RETURNS INT AS BEGIN RETURN 1; END; -> Option B

4. Quick Check:

   CREATE FUNCTION + RETURNS + BEGIN...END = correct syntax [OK]

Hint: Functions start with CREATE FUNCTION and specify RETURNS [OK]

Common Mistakes:

• Using CREATE TABLE instead of CREATE FUNCTION
• Trying to SELECT a function directly without calling it
• Confusing DROP FUNCTION with creation syntax

3. Given the function below, what will be the output of SELECT dbo.AddFive(10);?

CREATE FUNCTION dbo.AddFive(@num INT) RETURNS INT AS BEGIN RETURN @num + 5; END;

medium

A. 5

B. 10

C. 15

D. Syntax Error

Solution

1. Step 1: Understand the function logic

   The function takes an integer input and returns the input plus 5.

2. Step 2: Calculate the output for input 10

   10 + 5 = 15, so the function returns 15.

3. Final Answer:

   15 -> Option C

4. Quick Check:

   Input 10 + 5 = 15 [OK]

Hint: Add input to 5 as function returns input + 5 [OK]

Common Mistakes:

• Confusing input and output values
• Assuming function returns input unchanged
• Expecting syntax error due to missing parameters

4. Identify the error in the following user-defined function:

CREATE FUNCTION dbo.MultiplyByTwo(@val INT)
RETURNS INT
BEGIN
  SET @val = @val * 2;
  RETURN @val;
END;

medium

A. Missing AS keyword before BEGIN

B. Functions cannot modify input parameters

C. RETURN statement is not allowed in functions

D. Function must return VARCHAR, not INT

Solution

1. Step 1: Review function syntax rules

   In SQL, the AS keyword is required before the BEGIN block in function definitions.

2. Step 2: Check the given function

   The function is missing AS before BEGIN, causing a syntax error.

3. Final Answer:

   Missing AS keyword before BEGIN -> Option A

4. Quick Check:

   CREATE FUNCTION ... RETURNS ... AS BEGIN ... [OK]

Hint: Always include AS before BEGIN in function syntax [OK]

Common Mistakes:

• Trying to modify input parameters inside functions
• Omitting AS keyword before BEGIN
• Confusing function return types

5. You want to create a user-defined function that returns the length of a string only if the string is not empty; otherwise, it returns 0. Which of the following function definitions correctly implements this logic?

hard

A. CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN RETURN NULL; END;

B. CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN IF @input = '' RETURN 1; ELSE RETURN LEN(@input); END;

C. CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN RETURN CASE WHEN @input = '' THEN 1 ELSE LEN(@input) END; END;

D. CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN RETURN LEN(@input); END;

Solution

1. Step 1: Understand the requirement

   The function must return 0 if the string is empty, otherwise return its length.

2. Step 2: Analyze each option

   CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN RETURN LEN(@input); END; correctly returns LEN(@input), and since LEN('')=0 it satisfies the logic. CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN IF @input = '' RETURN 1; ELSE RETURN LEN(@input); END; returns 1 for empty strings, incorrect. CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN RETURN CASE WHEN @input = '' THEN 1 ELSE LEN(@input) END; END; returns 1 for empty strings using CASE, incorrect. CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN RETURN NULL; END; always returns NULL, which is incorrect.

3. Final Answer:

   CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN RETURN LEN(@input); END; -> Option D

4. Quick Check:

   LEN('') = 0 satisfies condition [OK]

Hint: LEN('') returns 0 for empty strings [OK]

Common Mistakes:

• Returning incorrect value (like 1) for empty strings
• Using overly complex conditional logic unnecessarily
• Returning NULL instead of 0 for empty strings