User-Defined Functions

Source data — Pattern 13: Database Objects

All queries run against the shared employees table (see schema.sql). This pattern introduces commonly used database objects including views, stored procedures, user-defined functions, and triggers. All examples are built using the existing employees table.

---

What it does

A user-defined function (UDF) accepts input parameters and returns a value or table that can be reused in SQL queries.

Problem

Create a function to calculate annual salary.

CREATE FUNCTION CalculateAnnualSalary(@MonthlySalary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
RETURN @MonthlySalary * 12;
END;

Result