How to Create a Function in SQL

Use a SQL function when logic should be reusable, parameterized, and return a value inside queries. Functions are perfect for reusable calculations, formatting helpers, and encapsulating repeated business rules.

What you’ll build or solve

You’ll learn how to create a function in SQL and call it inside SELECT, WHERE, or other expressions. You’ll also know when a function is better than a stored procedure.

When this approach works best

This approach is the right choice when the logic should return a value that can be reused in many queries.

Common real-world scenarios include:

• Tax calculations
• Discount logic
• Date formatting helpers
• String normalization
• Business score formulas

This is a bad idea when the workflow performs multi-step writes. In that case, use a stored procedure instead.

Prerequisites

You only need:

• Database support for SQL functions
• Permission to create programmable objects
• Basic SQL expression knowledge

Step-by-step instructions

Step 1: Create the function with parameters and a return value

The syntax differs by database. Here is a PostgreSQL-style example.

CREATE OR REPLACE FUNCTION calculate_tax(amount NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
  RETURN amount * 0.2;
END;
$$ LANGUAGE plpgsql;

Call it inside a query.

SELECT calculate_tax(total)
FROM invoices;

This keeps repeated calculation logic centralized.

What to look for:

• Functions return a value
• Parameters make them reusable
• Great inside SELECT and WHERE
• Best for reusable expressions
• Syntax differs by database

Examples you can copy

Discount helper

CREATE FUNCTION apply_discount(price NUMERIC)

Lowercase normalizer

CREATE FUNCTION normalize_email(email TEXT)

Score formula

CREATE FUNCTION lead_score(visits INT)

Common mistakes and how to fix them

Mistake 1: Using a function for multi-step write workflows

What the reader might do:

Build a checkout process inside a function.

Why it breaks: this mixes value-return logic with workflow orchestration.

Corrected approach:

Use stored procedures for multi-write workflows.

Mistake 2: Hardcoding business values

What the reader might do:

RETURN amount * 0.2

Why it breaks: tax rates may change.

Corrected approach:

Pass configurable values as parameters when appropriate.

Mistake 3: Overusing functions in large scans

What the reader might do:

Run complex custom functions on millions of rows.

Why it breaks: row-by-row execution can be expensive.

Corrected approach:

Benchmark and push simple logic into native SQL expressions where possible.

Troubleshooting

If the syntax errors, verify the database-specific function syntax.

If performance drops, inspect row-by-row execution cost.

If the logic writes data, switch to procedures.

If the function is reused widely, version and document it.

Quick recap

• Functions return reusable values
• Great for calculations and formatting
• Use parameters for flexibility
• Best inside query expressions
• Use procedures for multi-step writes