User-Defined Functions in T-SQL

After this lesson you can

• Create a scalar function and use it in a query
• Create an inline table-valued function and query it in the FROM
• Choose between a view, a stored procedure and a function

Key takeaways

• A scalar function returns one value and works anywhere a value goes in a query. Call it with its schema, as in dbo.LineTotal(UnitPrice, Quantity, Discount).
• In a function, RETURNS says the type it gives back, and RETURN gives back the value.
• A scalar function runs once per row, so save it for a formula worth sharing, not every calculation.
• An inline table-valued function returns rows and goes in the FROM: a view that takes parameters.
• Use a view to write a join once, a procedure for work with several steps or that changes data, and a function for a formula or a filtered table inside a query.

T-SQL comes with functions such as YEAR and ROUND. You can write your own, too: user-defined functions. Unlike a stored procedure, a function can be used inside a query.

A scalar function: one value

An order line's total, after its discount, is UnitPrice * Quantity * (1 - Discount). Every report that needs it has to get that formula right. A function writes it once:

CREATE OR ALTER FUNCTION dbo.LineTotal
(
    @UnitPrice money
    , @Quantity smallint
    , @Discount real
)
RETURNS money
AS
BEGIN
    RETURN @UnitPrice * @Quantity * (1 - @Discount);
END;

• RETURNS says the type of the value it gives back.
• RETURN gives back the value.
• The name starts with the schema, dbo.: a scalar function must be called with it.

Use it like a built-in function, anywhere a value goes:

SELECT TOP (4) OrderId, ProductId, UnitPrice, Quantity, Discount
    , dbo.LineTotal(UnitPrice, Quantity, Discount) AS LineTotal
FROM OrderDetails
WHERE Discount > 0
ORDER BY OrderId, ProductId;

It works inside aggregates too:

SELECT TOP (3) OrderId, SUM(dbo.LineTotal(UnitPrice, Quantity, Discount)) AS OrderTotal
FROM OrderDetails
GROUP BY OrderId
ORDER BY OrderTotal DESC;

A scalar function runs once per row, so on a big table it can be slow. Use one for a formula worth sharing, not for every calculation.

A table-valued function: rows

An inline table-valued function returns rows, so it's used in the FROM, like a table. Think of it as a view that takes parameters:

CREATE OR ALTER FUNCTION dbo.ProductsCheaperThan
(
    @Price money
)
RETURNS TABLE
AS
RETURN
    SELECT ProductId, ProductName, UnitPrice
    FROM Products
    WHERE UnitPrice < @Price;

RETURNS TABLE, then RETURN and a single SELECT: no BEGIN and END.

SELECT ProductName, UnitPrice
FROM dbo.ProductsCheaperThan(7)
ORDER BY UnitPrice;

Because it acts like a table, you can filter, join and group its rows like any other.

View, procedure or function?

Check yourself

3 questions to try on the lesson page.

So what

Views name a query, procedures name a piece of work, and functions name a formula or a filtered table you can use inside other queries. Pick by what you need: parameters, changes, or a place inside a SELECT.

Key terms

• User-Defined Function
• Table-Valued Function
• dbo Schema

Cheat sheets

• User-Defined Functions in T-SQL