User-Defined Functions — SQL Functions | OneNoughtOne

0/278

Database Management SystemsSQL Advanced Features

SQL Functions

LevelIntermediate

Duration75 mins

TopicSQL Advanced Features

1 / 5

User-Defined Functions

The Power of Custom Functions in SQL

In the world of database programming, user-defined functions (UDFs) represent one of the most powerful abstraction mechanisms available to database developers. While SQL provides an extensive library of built-in functions—from mathematical operations like ROUND() and ABS() to string manipulations like SUBSTRING() and UPPER() —the real power emerges when you can encapsulate your own business logic into reusable, callable units that integrate seamlessly with the SQL language itself. Consider this scenario: Your e-commerce database needs to calculate product discounts based on complex rules involving customer loyalty tier, purchase history, seasonal promotions, and inventory levels. Without UDFs, you would repeat this intricate calculation logic in every query that needs it—creating a maintenance nightmare where a single business rule change requires updates across dozens of queries and views. With UDFs, you write the logic once, test it thoroughly, and invoke it anywhere with a simple function call like CalculateDiscount(customer_id, product_id, quantity) .

What You Will Learn

By the end of this page, you will understand what user-defined functions are and why they exist, master the fundamental syntax for creating functions across major database systems, learn how to invoke functions in various SQL contexts, and appreciate the architectural role functions play in database application design. This knowledge forms the foundation for all subsequent pages in this module.

Understanding User-Defined Functions

A user-defined function (UDF) is a named, stored routine that accepts zero or more input parameters, performs a computation or data retrieval operation, and returns a value or a result set. Unlike stored procedures, which are primarily imperative programs that can modify database state and produce multiple result sets, functions are designed to be deterministic computational units that can be embedded directly within SQL expressions. The conceptual model of a function follows the mathematical definition: a mapping from input values to output values. When you invoke a function with the same inputs, you should—in most cases—receive the same output. This predictability is what allows the database optimizer to reason about functions and potentially cache their results or reorder operations for efficiency.

Core Characteristics of User-Defined Functions

•Named and Stored — Functions have unique names within a schema and are persistently stored in the database catalog, available for invocation by any authorized user or application.
•Parameterized — Functions accept input parameters that customize their behavior for each invocation. Parameters are typed, and the database enforces type safety at call time.
•Return-Value Oriented — Every function must return a value (scalar) or a result set (table-valued). This is the fundamental contract that distinguishes functions from procedures.
•Expression-Compatible — Functions can be invoked anywhere an expression of their return type is valid: in SELECT lists, WHERE clauses, JOIN conditions, CHECK constraints, and computed columns.
•Encapsulation Units — Functions hide implementation complexity behind a clean interface, enabling code reuse and centralizing business logic.

Functions vs. Expressions

At their core, UDFs extend the expression language of SQL. Just as you can write total * 0.1 to calculate a 10% tax, you can write CalculateTax(total, region) to apply region-specific tax rules. The function call is syntactically an expression and can appear wherever that expression type is valid.

Function Categories Overview

Before diving into creation syntax, it's essential to understand the landscape of user-defined functions. Database systems typically support several categories of functions, each with distinct characteristics, use cases, and performance implications. Understanding these categories guides you in choosing the right function type for each requirement. The primary classification axis is the return type : what kind of value does the function produce when invoked?

User-Defined Function Categories
Function Type	Returns	Usage Context	Key Characteristic
Scalar Function	Single value (INT, VARCHAR, DATE, etc.)	SELECT, WHERE, JOIN, CASE expressions	Row-by-row evaluation in queries
Table-Valued Function (TVF)	Result set (virtual table)	FROM clause, JOIN operations	Returns rows that can be queried
Inline TVF	Single SELECT statement result	FROM clause (inline expansion)	Optimizer can inline into calling query
Multi-Statement TVF	Populated table variable	FROM clause	More flexible logic, less optimizable
Aggregate Function	Single value from multiple rows	SELECT with GROUP BY	Custom aggregation logic (advanced)

The distinction matters for optimization: The database query optimizer treats different function types very differently. Inline table-valued functions can often be "flattened" into the calling query, allowing the optimizer to consider the entire query plan holistically. Scalar functions, by contrast, are typically executed row-by-row, which can create performance bottlenecks in large result sets. Multi-statement table-valued functions are treated as "black boxes" that the optimizer cannot peer into. This module dedicates separate pages to scalar functions and table-valued functions because their creation, usage patterns, and optimization considerations differ substantially.

Choosing the Right Function Type

When designing a function, start by asking: 'What am I returning?' If it's a single computed value (price, discount, formatted string), use a scalar function. If it's a set of rows (filtered subset, transformed data), use a table-valued function. This decision fundamentally shapes how the function integrates with SQL queries.

Function Creation Anatomy

Creating a user-defined function follows a consistent structural pattern across database systems, though syntax details vary. Understanding the anatomy of a function definition helps you write clear, maintainable, and portable code. A function definition consists of several key components that specify its identity, interface, behavior, and metadata:

function_anatomy.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Generic function structure (SQL Server syntax)
CREATE FUNCTION schema_name.function_name
(
    -- Parameter declarations
    @parameter1 datatype,
    @parameter2 datatype = default_value,  -- Optional default
    ... 
)
RETURNS return_datatype  -- What the function returns
[WITH function_options]   -- Metadata and behavior modifiers
AS
BEGIN
    -- Function body: local variables, logic, control flow
    DECLARE @result return_datatype;
    
    -- Computation logic here
    SET @result = ...;
    
    -- Every function MUST return a value
    RETURN @result;
END;

Function Definition Components

•CREATE FUNCTION — The DDL statement that registers the function in the database catalog. Use CREATE OR ALTER (SQL Server 2016+) or CREATE OR REPLACE (PostgreSQL, Oracle) for idempotent definitions.
•Schema and Name — Functions belong to schemas and must have unique names within that schema. Fully qualified names (schema.function_name) prevent ambiguity.
•Parameters — Input values passed to the function. Each parameter has a name (prefixed with @ in SQL Server) and a data type. Parameters can have default values, making them optional.
•RETURNS Clause — Specifies the data type of the value the function produces. For scalar functions, this is a primitive type. For table-valued functions, it's a table definition.
•WITH Options — Function metadata including SCHEMABINDING (prevents underlying object changes), ENCRYPTION (hides source code), and determinism declarations.
•Function Body — The actual logic: variable declarations, conditional statements, loops, and the computation that produces the return value.
•RETURN Statement — The required termination point that provides the function's output. Every execution path must reach a RETURN statement.

Every Path Must Return

Unlike some programming languages that allow implicit null returns, SQL functions typically require explicit RETURN statements on every execution path. A function that might not return a value will either fail at creation time or produce runtime errors.

Creating Your First Scalar Function

Let's create a practical scalar function that demonstrates the key concepts. We'll build a function that calculates the age of a person based on their birth date—a common requirement in many applications that involves date arithmetic and edge-case handling. This seemingly simple task actually requires careful consideration: we must handle future birth dates, account for whether the birthday has occurred this year, and deal with potential NULL inputs.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Calculate age in years from birth date
CREATE OR ALTER FUNCTION dbo.CalculateAge
(
    @BirthDate DATE
)
RETURNS INT
WITH SCHEMABINDING  -- Prevents changes to referenced objects
AS
BEGIN
    -- Handle NULL input gracefully
    IF @BirthDate IS NULL
        RETURN NULL;
    
    -- Handle future birth dates (invalid data)
    IF @BirthDate > GETDATE()
        RETURN NULL;
    
    DECLARE @Age INT;
    DECLARE @Today DATE = CAST(GETDATE() AS DATE);
    
    -- Calculate base age from year difference
    SET @Age = DATEDIFF(YEAR, @BirthDate, @Today);
    
    -- Adjust if birthday hasn't occurred this year
    -- Compare month and day only
    IF DATEADD(YEAR, @Age, @BirthDate) > @Today
        SET @Age = @Age - 1;
    
    RETURN @Age;
END;

Notice the common patterns across all implementations: NULL handling — All versions explicitly check for NULL input and return NULL rather than failing or returning incorrect values. Input validation — Future birth dates are caught and handled gracefully. Declarative metadata — Each database has keywords (SCHEMABINDING, IMMUTABLE, DETERMINISTIC) that describe function behavior to the optimizer. Clear return path — Every execution path explicitly returns a value.

Invoking Functions

Once created, functions become first-class citizens of the SQL expression language. You can invoke them anywhere an expression of the appropriate type is valid. Understanding the various invocation contexts helps you leverage functions effectively throughout your database applications.

function_invocation_contexts.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
-- Function invocation demonstrations (using CalculateAge example)
 
-- 1. In SELECT list: computed column for each row
SELECT 
    employee_id,
    first_name,
    last_name,
    birth_date,
    dbo.CalculateAge(birth_date) AS age
FROM employees;
 
-- 2. In WHERE clause: filtering based on computed value
SELECT employee_id, first_name, last_name
FROM employees
WHERE dbo.CalculateAge(birth_date) >= 18
  AND dbo.CalculateAge(birth_date) < 65;
 
-- 3. In ORDER BY: sorting by computed values
SELECT employee_id, first_name, hire_date
FROM employees
ORDER BY dbo.CalculateAge(birth_date) DESC;
 
-- 4. In CASE expressions: conditional logic
SELECT 
    employee_id,
    first_name,
    CASE 
        WHEN dbo.CalculateAge(birth_date) < 30 THEN 'Young'
        WHEN dbo.CalculateAge(birth_date) < 50 THEN 'Mid-Career'
        ELSE 'Senior'
    END AS age_category
FROM employees;
 
-- 5. In JOIN conditions
SELECT e.employee_id, e.first_name, b.benefit_plan
FROM employees e
JOIN age_based_benefits b 
    ON dbo.CalculateAge(e.birth_date) BETWEEN b.min_age AND b.max_age;
 
-- 6. In computed columns (persistent or virtual)
ALTER TABLE employees
ADD age AS dbo.CalculateAge(birth_date);  -- Virtual computed column
 
-- 7. In CHECK constraints (with SCHEMABINDING)
ALTER TABLE employees
ADD CONSTRAINT chk_adult 
    CHECK (dbo.CalculateAge(birth_date) >= 18);
 
-- 8. In views
CREATE VIEW v_employee_ages AS
SELECT 
    employee_id,
    first_name || ' ' || last_name AS full_name,
    dbo.CalculateAge(birth_date) AS current_age,
    birth_date
FROM employees;
 
-- 9. In INSERT statements
INSERT INTO audit_log (employee_id, age_at_action, action_date)
SELECT 
    employee_id, 
    dbo.CalculateAge(birth_date), 
    GETDATE()
FROM employees WHERE status = 'ACTIVE';
 
-- 10. In UPDATE statements
UPDATE employees
SET age_category = CASE 
    WHEN dbo.CalculateAge(birth_date) < 30 THEN 'JUNIOR'
    ELSE 'SENIOR'
END;

Function Invocation Best Practice

When using scalar functions in WHERE clauses on large tables, be aware that the function is typically evaluated for every row that reaches that point in query execution. This can be expensive. For frequently filtered calculations, consider persisted computed columns or indexed views that pre-compute the function result.

Function Parameters Deep Dive

Parameters form the interface contract between a function and its callers. Understanding parameter mechanics—including default values, type coercion, and parameter ordering—is essential for designing robust, user-friendly functions.

parameter_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
-- Comprehensive parameter demonstration
 
-- Function with multiple parameters and defaults
CREATE OR ALTER FUNCTION dbo.FormatCurrency
(
    @Amount DECIMAL(18, 4),
    @CurrencyCode CHAR(3) = 'USD',      -- Default value
    @IncludeSymbol BIT = 1,              -- Default to true
    @DecimalPlaces INT = 2               -- Default precision
)
RETURNS NVARCHAR(50)
AS
BEGIN
    DECLARE @Result NVARCHAR(50);
    DECLARE @Symbol NVARCHAR(5);
    
    -- Determine currency symbol
    SET @Symbol = CASE @CurrencyCode
        WHEN 'USD' THEN '$'
        WHEN 'EUR' THEN '€'
        WHEN 'GBP' THEN '£'
        WHEN 'JPY' THEN '¥'
        WHEN 'INR' THEN '₹'
        ELSE @CurrencyCode + ' '
    END;
    
    -- Format the amount with specified decimal places
    SET @Result = FORMAT(@Amount, 'N' + CAST(@DecimalPlaces AS VARCHAR));
    
    -- Prepend symbol if requested
    IF @IncludeSymbol = 1
        SET @Result = @Symbol + @Result;
    
    RETURN @Result;
END;
 
-- Invocation examples:
 
-- Using all defaults (USD, with symbol, 2 decimals)
SELECT dbo.FormatCurrency(1234.5678);
-- Result: $1,234.57
 
-- Specifying currency only
SELECT dbo.FormatCurrency(1234.5678, 'EUR');
-- Result: €1,234.57
 
-- Named parameters for clarity (SQL Server)
SELECT dbo.FormatCurrency(
    @Amount = 1234.5678,
    @CurrencyCode = 'GBP',
    @IncludeSymbol = 0,
    @DecimalPlaces = 4
);
-- Result: 1,234.5678
 
-- Skipping middle parameters with named syntax
SELECT dbo.FormatCurrency(
    @Amount = 1234.5,
    @DecimalPlaces = 0  -- Skip CurrencyCode and IncludeSymbol
);
-- Result: $1,235

Parameter Best Practices

•Order by frequency of customization — Place parameters that callers most commonly override first, and those with sensible defaults last.
•Use meaningful default values — Defaults should represent the most common use case, reducing verbosity for typical invocations.
•Validate parameters early — Check for invalid inputs at the start of the function body and return appropriate values or raise errors.
•Consider named parameters — When functions have many parameters, named parameter syntax improves readability and allows skipping defaults.
•Document parameter contracts — Use comments or separate documentation to explain what each parameter means, valid ranges, and behavior with edge values.

The RETURN Statement

The RETURN statement is the culmination of function execution—it terminates the function and provides the computed value back to the caller. While conceptually simple, the RETURN statement has nuances that affect function design and behavior.

return_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
-- Pattern 1: Single return at end (recommended for clarity)
CREATE FUNCTION dbo.CalculateDiscount(@Price DECIMAL(10,2), @Quantity INT)
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @Discount DECIMAL(10,2) = 0;
    
    IF @Quantity >= 100
        SET @Discount = @Price * 0.20;
    ELSE IF @Quantity >= 50
        SET @Discount = @Price * 0.10;
    ELSE IF @Quantity >= 10
        SET @Discount = @Price * 0.05;
    
    RETURN @Discount;  -- Single exit point
END;
 
-- Pattern 2: Early returns for guard clauses (acceptable)
CREATE FUNCTION dbo.SafeDivide(@Numerator DECIMAL(18,6), @Denominator DECIMAL(18,6))
RETURNS DECIMAL(18,6)
AS
BEGIN
    -- Guard clause: return NULL for division by zero
    IF @Denominator = 0
        RETURN NULL;
    
    -- Guard clause: return NULL for NULL inputs
    IF @Numerator IS NULL OR @Denominator IS NULL
        RETURN NULL;
    
    -- Main logic
    RETURN @Numerator / @Denominator;
END;
 
-- Pattern 3: Return in conditional branches (use carefully)
CREATE FUNCTION dbo.GetTaxRate(@StateCode CHAR(2))
RETURNS DECIMAL(5,4)
AS
BEGIN
    -- Each branch must return
    IF @StateCode = 'CA' RETURN 0.0725;
    IF @StateCode = 'TX' RETURN 0.0625;
    IF @StateCode = 'NY' RETURN 0.0800;
    IF @StateCode = 'FL' RETURN 0.0600;
    
    -- Default case (required!)
    RETURN 0.0000;
END;
 
-- Anti-pattern: Missing return path (ERROR or unpredictable)
-- CREATE FUNCTION dbo.BrokenFunction(@Input INT)
-- RETURNS INT
-- AS
-- BEGIN
--     IF @Input > 0
--         RETURN @Input * 2;
--     -- What if @Input <= 0? No return! This is an ERROR.
-- END;

RETURN vs. SELECT in Functions

In scalar functions, RETURN provides the value; you cannot use SELECT to output results. SELECT statements in scalar functions are only for assigning to variables. Table-valued functions have different semantics where SELECT populates the returned table.

Database Management SystemsSQL Advanced Features

SQL Functions

LevelIntermediate

Duration75 mins

TopicSQL Advanced Features

1 / 5

User-Defined Functions

The Power of Custom Functions in SQL

In the world of database programming, user-defined functions (UDFs) represent one of the most powerful abstraction mechanisms available to database developers. While SQL provides an extensive library of built-in functions—from mathematical operations like ROUND() and ABS() to string manipulations like SUBSTRING() and UPPER() —the real power emerges when you can encapsulate your own business logic into reusable, callable units that integrate seamlessly with the SQL language itself. Consider this scenario: Your e-commerce database needs to calculate product discounts based on complex rules involving customer loyalty tier, purchase history, seasonal promotions, and inventory levels. Without UDFs, you would repeat this intricate calculation logic in every query that needs it—creating a maintenance nightmare where a single business rule change requires updates across dozens of queries and views. With UDFs, you write the logic once, test it thoroughly, and invoke it anywhere with a simple function call like CalculateDiscount(customer_id, product_id, quantity) .

What You Will Learn

By the end of this page, you will understand what user-defined functions are and why they exist, master the fundamental syntax for creating functions across major database systems, learn how to invoke functions in various SQL contexts, and appreciate the architectural role functions play in database application design. This knowledge forms the foundation for all subsequent pages in this module.

Understanding User-Defined Functions

A user-defined function (UDF) is a named, stored routine that accepts zero or more input parameters, performs a computation or data retrieval operation, and returns a value or a result set. Unlike stored procedures, which are primarily imperative programs that can modify database state and produce multiple result sets, functions are designed to be deterministic computational units that can be embedded directly within SQL expressions. The conceptual model of a function follows the mathematical definition: a mapping from input values to output values. When you invoke a function with the same inputs, you should—in most cases—receive the same output. This predictability is what allows the database optimizer to reason about functions and potentially cache their results or reorder operations for efficiency.

Core Characteristics of User-Defined Functions

•Named and Stored — Functions have unique names within a schema and are persistently stored in the database catalog, available for invocation by any authorized user or application.
•Parameterized — Functions accept input parameters that customize their behavior for each invocation. Parameters are typed, and the database enforces type safety at call time.
•Return-Value Oriented — Every function must return a value (scalar) or a result set (table-valued). This is the fundamental contract that distinguishes functions from procedures.
•Expression-Compatible — Functions can be invoked anywhere an expression of their return type is valid: in SELECT lists, WHERE clauses, JOIN conditions, CHECK constraints, and computed columns.
•Encapsulation Units — Functions hide implementation complexity behind a clean interface, enabling code reuse and centralizing business logic.

Functions vs. Expressions

At their core, UDFs extend the expression language of SQL. Just as you can write total * 0.1 to calculate a 10% tax, you can write CalculateTax(total, region) to apply region-specific tax rules. The function call is syntactically an expression and can appear wherever that expression type is valid.

Function Categories Overview

Before diving into creation syntax, it's essential to understand the landscape of user-defined functions. Database systems typically support several categories of functions, each with distinct characteristics, use cases, and performance implications. Understanding these categories guides you in choosing the right function type for each requirement. The primary classification axis is the return type : what kind of value does the function produce when invoked?

User-Defined Function Categories
Function Type	Returns	Usage Context	Key Characteristic
Scalar Function	Single value (INT, VARCHAR, DATE, etc.)	SELECT, WHERE, JOIN, CASE expressions	Row-by-row evaluation in queries
Table-Valued Function (TVF)	Result set (virtual table)	FROM clause, JOIN operations	Returns rows that can be queried
Inline TVF	Single SELECT statement result	FROM clause (inline expansion)	Optimizer can inline into calling query
Multi-Statement TVF	Populated table variable	FROM clause	More flexible logic, less optimizable
Aggregate Function	Single value from multiple rows	SELECT with GROUP BY	Custom aggregation logic (advanced)

The distinction matters for optimization: The database query optimizer treats different function types very differently. Inline table-valued functions can often be "flattened" into the calling query, allowing the optimizer to consider the entire query plan holistically. Scalar functions, by contrast, are typically executed row-by-row, which can create performance bottlenecks in large result sets. Multi-statement table-valued functions are treated as "black boxes" that the optimizer cannot peer into. This module dedicates separate pages to scalar functions and table-valued functions because their creation, usage patterns, and optimization considerations differ substantially.

Choosing the Right Function Type

When designing a function, start by asking: 'What am I returning?' If it's a single computed value (price, discount, formatted string), use a scalar function. If it's a set of rows (filtered subset, transformed data), use a table-valued function. This decision fundamentally shapes how the function integrates with SQL queries.

Function Creation Anatomy

Creating a user-defined function follows a consistent structural pattern across database systems, though syntax details vary. Understanding the anatomy of a function definition helps you write clear, maintainable, and portable code. A function definition consists of several key components that specify its identity, interface, behavior, and metadata:

function_anatomy.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Generic function structure (SQL Server syntax)
CREATE FUNCTION schema_name.function_name
(
    -- Parameter declarations
    @parameter1 datatype,
    @parameter2 datatype = default_value,  -- Optional default
    ... 
)
RETURNS return_datatype  -- What the function returns
[WITH function_options]   -- Metadata and behavior modifiers
AS
BEGIN
    -- Function body: local variables, logic, control flow
    DECLARE @result return_datatype;
    
    -- Computation logic here
    SET @result = ...;
    
    -- Every function MUST return a value
    RETURN @result;
END;

Function Definition Components

•CREATE FUNCTION — The DDL statement that registers the function in the database catalog. Use CREATE OR ALTER (SQL Server 2016+) or CREATE OR REPLACE (PostgreSQL, Oracle) for idempotent definitions.
•Schema and Name — Functions belong to schemas and must have unique names within that schema. Fully qualified names (schema.function_name) prevent ambiguity.
•Parameters — Input values passed to the function. Each parameter has a name (prefixed with @ in SQL Server) and a data type. Parameters can have default values, making them optional.
•RETURNS Clause — Specifies the data type of the value the function produces. For scalar functions, this is a primitive type. For table-valued functions, it's a table definition.
•WITH Options — Function metadata including SCHEMABINDING (prevents underlying object changes), ENCRYPTION (hides source code), and determinism declarations.
•Function Body — The actual logic: variable declarations, conditional statements, loops, and the computation that produces the return value.
•RETURN Statement — The required termination point that provides the function's output. Every execution path must reach a RETURN statement.

Every Path Must Return

Unlike some programming languages that allow implicit null returns, SQL functions typically require explicit RETURN statements on every execution path. A function that might not return a value will either fail at creation time or produce runtime errors.

Creating Your First Scalar Function

Let's create a practical scalar function that demonstrates the key concepts. We'll build a function that calculates the age of a person based on their birth date—a common requirement in many applications that involves date arithmetic and edge-case handling. This seemingly simple task actually requires careful consideration: we must handle future birth dates, account for whether the birthday has occurred this year, and deal with potential NULL inputs.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Calculate age in years from birth date
CREATE OR ALTER FUNCTION dbo.CalculateAge
(
    @BirthDate DATE
)
RETURNS INT
WITH SCHEMABINDING  -- Prevents changes to referenced objects
AS
BEGIN
    -- Handle NULL input gracefully
    IF @BirthDate IS NULL
        RETURN NULL;
    
    -- Handle future birth dates (invalid data)
    IF @BirthDate > GETDATE()
        RETURN NULL;
    
    DECLARE @Age INT;
    DECLARE @Today DATE = CAST(GETDATE() AS DATE);
    
    -- Calculate base age from year difference
    SET @Age = DATEDIFF(YEAR, @BirthDate, @Today);
    
    -- Adjust if birthday hasn't occurred this year
    -- Compare month and day only
    IF DATEADD(YEAR, @Age, @BirthDate) > @Today
        SET @Age = @Age - 1;
    
    RETURN @Age;
END;

Notice the common patterns across all implementations: NULL handling — All versions explicitly check for NULL input and return NULL rather than failing or returning incorrect values. Input validation — Future birth dates are caught and handled gracefully. Declarative metadata — Each database has keywords (SCHEMABINDING, IMMUTABLE, DETERMINISTIC) that describe function behavior to the optimizer. Clear return path — Every execution path explicitly returns a value.

Invoking Functions

Once created, functions become first-class citizens of the SQL expression language. You can invoke them anywhere an expression of the appropriate type is valid. Understanding the various invocation contexts helps you leverage functions effectively throughout your database applications.

function_invocation_contexts.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
-- Function invocation demonstrations (using CalculateAge example)
 
-- 1. In SELECT list: computed column for each row
SELECT 
    employee_id,
    first_name,
    last_name,
    birth_date,
    dbo.CalculateAge(birth_date) AS age
FROM employees;
 
-- 2. In WHERE clause: filtering based on computed value
SELECT employee_id, first_name, last_name
FROM employees
WHERE dbo.CalculateAge(birth_date) >= 18
  AND dbo.CalculateAge(birth_date) < 65;
 
-- 3. In ORDER BY: sorting by computed values
SELECT employee_id, first_name, hire_date
FROM employees
ORDER BY dbo.CalculateAge(birth_date) DESC;
 
-- 4. In CASE expressions: conditional logic
SELECT 
    employee_id,
    first_name,
    CASE 
        WHEN dbo.CalculateAge(birth_date) < 30 THEN 'Young'
        WHEN dbo.CalculateAge(birth_date) < 50 THEN 'Mid-Career'
        ELSE 'Senior'
    END AS age_category
FROM employees;
 
-- 5. In JOIN conditions
SELECT e.employee_id, e.first_name, b.benefit_plan
FROM employees e
JOIN age_based_benefits b 
    ON dbo.CalculateAge(e.birth_date) BETWEEN b.min_age AND b.max_age;
 
-- 6. In computed columns (persistent or virtual)
ALTER TABLE employees
ADD age AS dbo.CalculateAge(birth_date);  -- Virtual computed column
 
-- 7. In CHECK constraints (with SCHEMABINDING)
ALTER TABLE employees
ADD CONSTRAINT chk_adult 
    CHECK (dbo.CalculateAge(birth_date) >= 18);
 
-- 8. In views
CREATE VIEW v_employee_ages AS
SELECT 
    employee_id,
    first_name || ' ' || last_name AS full_name,
    dbo.CalculateAge(birth_date) AS current_age,
    birth_date
FROM employees;
 
-- 9. In INSERT statements
INSERT INTO audit_log (employee_id, age_at_action, action_date)
SELECT 
    employee_id, 
    dbo.CalculateAge(birth_date), 
    GETDATE()
FROM employees WHERE status = 'ACTIVE';
 
-- 10. In UPDATE statements
UPDATE employees
SET age_category = CASE 
    WHEN dbo.CalculateAge(birth_date) < 30 THEN 'JUNIOR'
    ELSE 'SENIOR'
END;

Function Invocation Best Practice

When using scalar functions in WHERE clauses on large tables, be aware that the function is typically evaluated for every row that reaches that point in query execution. This can be expensive. For frequently filtered calculations, consider persisted computed columns or indexed views that pre-compute the function result.

Function Parameters Deep Dive

Parameters form the interface contract between a function and its callers. Understanding parameter mechanics—including default values, type coercion, and parameter ordering—is essential for designing robust, user-friendly functions.

parameter_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
-- Comprehensive parameter demonstration
 
-- Function with multiple parameters and defaults
CREATE OR ALTER FUNCTION dbo.FormatCurrency
(
    @Amount DECIMAL(18, 4),
    @CurrencyCode CHAR(3) = 'USD',      -- Default value
    @IncludeSymbol BIT = 1,              -- Default to true
    @DecimalPlaces INT = 2               -- Default precision
)
RETURNS NVARCHAR(50)
AS
BEGIN
    DECLARE @Result NVARCHAR(50);
    DECLARE @Symbol NVARCHAR(5);
    
    -- Determine currency symbol
    SET @Symbol = CASE @CurrencyCode
        WHEN 'USD' THEN '$'
        WHEN 'EUR' THEN '€'
        WHEN 'GBP' THEN '£'
        WHEN 'JPY' THEN '¥'
        WHEN 'INR' THEN '₹'
        ELSE @CurrencyCode + ' '
    END;
    
    -- Format the amount with specified decimal places
    SET @Result = FORMAT(@Amount, 'N' + CAST(@DecimalPlaces AS VARCHAR));
    
    -- Prepend symbol if requested
    IF @IncludeSymbol = 1
        SET @Result = @Symbol + @Result;
    
    RETURN @Result;
END;
 
-- Invocation examples:
 
-- Using all defaults (USD, with symbol, 2 decimals)
SELECT dbo.FormatCurrency(1234.5678);
-- Result: $1,234.57
 
-- Specifying currency only
SELECT dbo.FormatCurrency(1234.5678, 'EUR');
-- Result: €1,234.57
 
-- Named parameters for clarity (SQL Server)
SELECT dbo.FormatCurrency(
    @Amount = 1234.5678,
    @CurrencyCode = 'GBP',
    @IncludeSymbol = 0,
    @DecimalPlaces = 4
);
-- Result: 1,234.5678
 
-- Skipping middle parameters with named syntax
SELECT dbo.FormatCurrency(
    @Amount = 1234.5,
    @DecimalPlaces = 0  -- Skip CurrencyCode and IncludeSymbol
);
-- Result: $1,235

Parameter Best Practices

•Order by frequency of customization — Place parameters that callers most commonly override first, and those with sensible defaults last.
•Use meaningful default values — Defaults should represent the most common use case, reducing verbosity for typical invocations.
•Validate parameters early — Check for invalid inputs at the start of the function body and return appropriate values or raise errors.
•Consider named parameters — When functions have many parameters, named parameter syntax improves readability and allows skipping defaults.
•Document parameter contracts — Use comments or separate documentation to explain what each parameter means, valid ranges, and behavior with edge values.

The RETURN Statement

The RETURN statement is the culmination of function execution—it terminates the function and provides the computed value back to the caller. While conceptually simple, the RETURN statement has nuances that affect function design and behavior.

return_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
-- Pattern 1: Single return at end (recommended for clarity)
CREATE FUNCTION dbo.CalculateDiscount(@Price DECIMAL(10,2), @Quantity INT)
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @Discount DECIMAL(10,2) = 0;
    
    IF @Quantity >= 100
        SET @Discount = @Price * 0.20;
    ELSE IF @Quantity >= 50
        SET @Discount = @Price * 0.10;
    ELSE IF @Quantity >= 10
        SET @Discount = @Price * 0.05;
    
    RETURN @Discount;  -- Single exit point
END;
 
-- Pattern 2: Early returns for guard clauses (acceptable)
CREATE FUNCTION dbo.SafeDivide(@Numerator DECIMAL(18,6), @Denominator DECIMAL(18,6))
RETURNS DECIMAL(18,6)
AS
BEGIN
    -- Guard clause: return NULL for division by zero
    IF @Denominator = 0
        RETURN NULL;
    
    -- Guard clause: return NULL for NULL inputs
    IF @Numerator IS NULL OR @Denominator IS NULL
        RETURN NULL;
    
    -- Main logic
    RETURN @Numerator / @Denominator;
END;
 
-- Pattern 3: Return in conditional branches (use carefully)
CREATE FUNCTION dbo.GetTaxRate(@StateCode CHAR(2))
RETURNS DECIMAL(5,4)
AS
BEGIN
    -- Each branch must return
    IF @StateCode = 'CA' RETURN 0.0725;
    IF @StateCode = 'TX' RETURN 0.0625;
    IF @StateCode = 'NY' RETURN 0.0800;
    IF @StateCode = 'FL' RETURN 0.0600;
    
    -- Default case (required!)
    RETURN 0.0000;
END;
 
-- Anti-pattern: Missing return path (ERROR or unpredictable)
-- CREATE FUNCTION dbo.BrokenFunction(@Input INT)
-- RETURNS INT
-- AS
-- BEGIN
--     IF @Input > 0
--         RETURN @Input * 2;
--     -- What if @Input <= 0? No return! This is an ERROR.
-- END;

RETURN vs. SELECT in Functions

In scalar functions, RETURN provides the value; you cannot use SELECT to output results. SELECT statements in scalar functions are only for assigning to variables. Table-valued functions have different semantics where SELECT populates the returned table.

User-Defined Functions

The Power of Custom Functions in SQL

Understanding User-Defined Functions

Function Categories Overview

Function Creation Anatomy

Creating Your First Scalar Function

Invoking Functions

Function Parameters Deep Dive

The RETURN Statement

There’s more to understand.

User-Defined Functions

The Power of Custom Functions in SQL

Understanding User-Defined Functions

Function Categories Overview

Function Creation Anatomy

Creating Your First Scalar Function

Invoking Functions

Function Parameters Deep Dive

The RETURN Statement

There’s more to understand.