# User-Defined Functions in T-SQL

Write your own functions. A scalar function returns one value and works inside any query; a table-valued function returns rows and works like a table that takes parameters.

Journeyman · T-SQL · SQL · Updated November 3, 2026

## 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:

```sql
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:

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

| OrderId | ProductId | UnitPrice | Quantity | Discount | LineTotal |
|---|---|---|---|---|---|
| 10250 | 51 | 42.40 | 35 | 0.15 | 1261.40 |
| 10250 | 65 | 16.80 | 15 | 0.15 | 214.20 |
| 10251 | 22 | 16.80 | 6 | 0.05 | 95.76 |
| 10251 | 57 | 15.60 | 15 | 0.05 | 222.30 |

It works inside aggregates too:

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

| OrderId | OrderTotal |
|---|---|
| 10865 | 16387.50 |
| 10981 | 15810.00 |
| 11030 | 12615.05 |

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:

```sql
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`.

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

| ProductName | UnitPrice |
|---|---|
| Geitost | 2.50 |
| Guaraná Fantástica | 4.50 |
| Konbu | 6.00 |

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

## View, procedure or function?

| | View | Stored procedure | Function |
|---|---|---|---|
| Takes parameters | No | Yes | Yes |
| Used inside a `SELECT` | Yes, in the `FROM` | No: run with `EXEC` | Yes: scalar as a value, table-valued in the `FROM` |
| Can hold statements that change data | No: one `SELECT` | Yes | No |
| Good for | A join or filter written once | Work with several steps, or that changes data | A formula, or a filtered table, used inside queries |

## 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](https://www.ask-codey.com/cheat-sheets/user-defined-functions-in-t-sql-cheat-sheet)

Source: https://www.ask-codey.com/lessons/user-defined-functions-in-t-sql