Lesson 14 — Programmability

User-Defined Functions

T-SQL lets you define your own reusable functions that return either a single scalar value or an entire table, callable from within other queries.

Scalar Functions

A scalar function returns a single value and can be used anywhere an expression is valid, such as inside a SELECT list or a WHERE clause.

T-SQL — A scalar function 
CREATE FUNCTION dbo.CalculateAge (@BirthDate DATE)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @BirthDate, GETDATE());
END;

SELECT FirstName, dbo.CalculateAge(BirthDate) AS Age
FROM Students;

Table-Valued Functions

A table-valued function returns an entire result set, and can be queried in the FROM clause exactly like a table or view, optionally accepting parameters that a plain view cannot.

Common Mistakes

  • Forgetting that scalar functions called row-by-row in a large query can hurt performance significantly compared to an equivalent inline calculation.
  • Confusing a function (must return a value, called within an expression) with a stored procedure (executed as a standalone statement).
  • Not schema-qualifying function calls (e.g. omitting dbo.), which some contexts require.
  • Writing a function with side effects (like modifying data), which T-SQL scalar/table functions are not designed for.

Professional Tip

For heavy-duty logic used across many queries, benchmark scalar functions against inline calculations on large datasets — the convenience is real, but so is the potential performance cost when a function is invoked once per row.

Your Turn

Write a scalar function called dbo.GetDiscountedPrice that accepts a price and a discount percentage and returns the discounted amount, then use it in a SELECT query.

Mini Quiz

What is the key difference between a stored procedure and a scalar function in T-SQL?