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.

Function Metadata and Options

Functions support various metadata options that affect their behavior, security, and optimization. These options tell the database engine important facts about how the function behaves, enabling better query plans and enforcing safety constraints.

Common Function Options Across Database Systems
Option	SQL Server	PostgreSQL	Oracle	Effect
Deterministic	WITH SCHEMABINDING*	IMMUTABLE	DETERMINISTIC	Same inputs always produce same output; enables caching
Stability (reads data)		STABLE		Returns same result within a transaction
Volatile		VOLATILE		Can return different results on each call
Schema binding	WITH SCHEMABINDING			Prevents modification of referenced objects
Parallel safe		PARALLEL SAFE	PARALLEL_ENABLE	Can be executed in parallel operations
Security	WITH ENCRYPTION			Hides function source code
NULL handling	RETURNS NULL ON NULL INPUT	RETURNS NULL ON NULL INPUT		Auto-return NULL if any input is NULL

function_options.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
-- SQL Server: Schema binding for computed columns and indexed views
CREATE FUNCTION dbo.ComputeHash(@Input NVARCHAR(MAX))
RETURNS VARBINARY(64)
WITH SCHEMABINDING,  -- Required for indexed views
     RETURNS NULL ON NULL INPUT  -- Automatic NULL propagation
AS
BEGIN
    RETURN HASHBYTES('SHA2_256', @Input);
END;
 
-- PostgreSQL: Full volatility and parallel specification
CREATE OR REPLACE FUNCTION calculate_compound_interest(
    principal NUMERIC,
    rate NUMERIC,
    periods INTEGER
)
RETURNS NUMERIC
LANGUAGE SQL
IMMUTABLE           -- Always same result for same inputs
PARALLEL SAFE       -- Can run in parallel queries
RETURNS NULL ON NULL INPUT  -- NULL in, NULL out
AS $$
    SELECT principal * POWER(1 + rate, periods);
$$;
 
-- Oracle: Optimizer hints through DETERMINISTIC
CREATE OR REPLACE FUNCTION calculate_tax(
    p_amount IN NUMBER,
    p_rate IN NUMBER
) RETURN NUMBER
DETERMINISTIC       -- Enables result caching
PARALLEL_ENABLE     -- Safe for parallel DML
AS
BEGIN
    RETURN p_amount * p_rate;
END;
/

Declare Determinism Accurately

Only mark functions as deterministic/immutable if they truly are. A function that reads from tables, uses GETDATE(), generates random values, or depends on session settings is NOT deterministic. Falsely declaring determinism can cause incorrect query results due to optimizer caching.

Error Handling in Functions

Error handling in functions differs from stored procedures because functions cannot alter the flow of the calling query—they must return a value. This constraint shapes how errors are managed: through defensive validation, NULL returns for invalid inputs, or allowing errors to propagate up to the caller.

function_error_handling.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
-- Strategy 1: Defensive NULL returns (safest for queries)
CREATE FUNCTION dbo.ParseInteger(@Input NVARCHAR(100))
RETURNS INT
AS
BEGIN
    -- Validate input format before conversion
    IF @Input IS NULL OR @Input = ''
        RETURN NULL;
    
    -- Check for valid numeric characters only
    IF @Input LIKE '%[^0-9-]%'
        RETURN NULL;
    
    -- Check for misplaced minus sign
    IF CHARINDEX('-', @Input) > 1
        RETURN NULL;
    
    -- Safe conversion (TRY_CAST would be simpler in SQL Server 2012+)
    BEGIN TRY
        RETURN CAST(@Input AS INT);
    END TRY
    BEGIN CATCH
        RETURN NULL;  -- Return NULL on conversion failure
    END CATCH
END;
 
-- Strategy 2: Using TRY_CAST/TRY_CONVERT (SQL Server 2012+)
CREATE FUNCTION dbo.SafeParseDecimal(@Input NVARCHAR(100))
RETURNS DECIMAL(18, 4)
AS
BEGIN
    RETURN TRY_CAST(@Input AS DECIMAL(18, 4));
    -- TRY_CAST returns NULL on failure instead of error
END;
 
-- PostgreSQL: Using exception handling
CREATE OR REPLACE FUNCTION safe_parse_json(input TEXT)
RETURNS JSONB
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN input::JSONB;
EXCEPTION
    WHEN OTHERS THEN
        -- Return NULL or empty JSON on parse failure
        RETURN NULL;
END;
$$;
 
-- Strategy 3: Error propagation (let caller handle)
CREATE FUNCTION dbo.StrictDivide(@Num DECIMAL(18,6), @Denom DECIMAL(18,6))
RETURNS DECIMAL(18,6)
AS
BEGIN
    -- No validation: division by zero will raise error
    -- Caller must handle or prevent invalid inputs
    RETURN @Num / @Denom;
END;

Defensive Approach (Return NULL)

•Queries continue executing
•Invalid data produces NULL values
•Good for data cleansing scenarios
•May hide data quality issues

Strict Approach (Propagate Errors)

•Errors halt query execution
•Invalid data is immediately visible
•Good for data validation enforcement
•Requires upstream error handling

Summary: User-Defined Functions Foundation

This page has established the foundational concepts of user-defined functions in SQL. You now understand what UDFs are, why they exist, and how to create and invoke them. This knowledge prepares you for the deeper dives into specific function types in subsequent pages.

Key Takeaways

•UDFs encapsulate reusable logic — Functions allow you to write business logic once and invoke it anywhere in SQL expressions, promoting code reuse and maintainability.
•Functions return values — Unlike procedures, functions must return a value (scalar or table), making them usable as expressions within SQL statements.
•Function types matter — Scalar functions return single values; table-valued functions return result sets. The choice impacts how functions integrate with queries and how the optimizer handles them.
•Metadata options guide optimization — Declaring functions as deterministic, schema-bound, or parallel-safe helps the database optimizer make better decisions.
•Error handling requires design decisions — Choose between defensive NULL-returning functions or strict error-propagating functions based on your application's needs.
•Syntax varies by platform — While concepts are universal, creation syntax differs across SQL Server, PostgreSQL, MySQL, and Oracle. Understanding one helps learn others.

Page Complete

You now have a solid foundation in user-defined functions. The next page dives deep into scalar functions—the most common function type—covering advanced patterns, performance considerations, and real-world use cases that will make you an effective function developer.