Functions

A function takes inputs and returns a value. Unlike a procedure, it is used inside a query — in a SELECT list, a WHERE clause, or a FROM clause — which is what makes it composable.

That difference decides which one you want. A procedure performs an action. A function produces a value that a query consumes.

CREATE OR ALTER FUNCTION fn_line_total
(
    @quantity   INT,
    @unit_price DECIMAL(10,2)
)
RETURNS DECIMAL(12,2)
WITH SCHEMABINDING
AS
BEGIN
    RETURN ISNULL(@quantity, 0) * ISNULL(@unit_price, 0);
END;

-- Used like a built-in function
SELECT
    oi.order_id,
    oi.product_id,
    dbo.fn_line_total(oi.quantity, oi.unit_price) AS line_total
FROM order_items AS oi
WHERE oi.order_id = 1043;

• RETURNS declares the type of the single value that comes back. The body must return that type on every path.
• In SQL Server a scalar function is called with its schema prefix — dbo.fn_line_total, not fn_line_total.
• WITH SCHEMABINDING stops anyone dropping or altering the objects the function depends on while it exists. It also lets the optimiser reason about the function better, which matters for the performance point below.
• ISNULL guards against a null input producing a null total. Whether that is right depends on the business question: treating a missing quantity as zero is a decision, and one worth making explicitly rather than inheriting from NULL arithmetic.

A table-valued function returns rows, so you use it in a FROM clause. It is close to a view that takes parameters, which is exactly the gap it fills — a view cannot be parameterised.

There are two kinds, and the difference matters more than the syntax suggests.

CREATE OR ALTER FUNCTION fn_orders_in_period
(
    @from_date DATE,
    @to_date   DATE
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        o.order_id,
        o.customer_id,
        o.order_date,
        o.status,
        SUM(oi.quantity * oi.unit_price) + o.shipping_fee AS order_total
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    WHERE o.order_date >= @from_date
      AND o.order_date <  @to_date
      AND o.status <> 'cancelled'
    GROUP BY o.order_id, o.customer_id, o.order_date, o.status, o.shipping_fee
);

-- Used in FROM, like a parameterised view
SELECT c.company_name, COUNT(*) AS orders, SUM(f.order_total) AS total
FROM fn_orders_in_period('2026-01-01', '2026-04-01') AS f
JOIN customers AS c ON c.customer_id = f.customer_id
GROUP BY c.company_name
ORDER BY total DESC;

• There is no BEGIN and END, and no variable declarations. The whole body is one RETURN with one SELECT, which is what makes it inline.
• That form lets the optimiser expand the function's definition into the calling query, so the outer WHERE and JOIN can influence how the inner query runs. It behaves much like a view with parameters.
• Called without a schema prefix in FROM, unlike a scalar function. The inconsistency is a quirk of T-SQL rather than a rule with a reason.

CREATE OR ALTER FUNCTION fn_customer_ranking (@from_date DATE)
RETURNS @result TABLE
(
    customer_id  INT,
    total_spend  DECIMAL(14,2),
    spend_band   NVARCHAR(10)
)
AS
BEGIN
    INSERT INTO @result (customer_id, total_spend, spend_band)
    SELECT
        o.customer_id,
        SUM(oi.quantity * oi.unit_price),
        CASE
            WHEN SUM(oi.quantity * oi.unit_price) >= 50000 THEN 'high'
            WHEN SUM(oi.quantity * oi.unit_price) >= 10000 THEN 'medium'
            ELSE 'low'
        END
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    WHERE o.order_date >= @from_date
      AND o.status <> 'cancelled'
    GROUP BY o.customer_id;

    RETURN;
END;

• This form declares the shape of the returned table, fills it with one or more statements, then returns it. You can do more: several inserts, conditions, intermediate variables.
• The cost is that the optimiser cannot see inside. It treats the function as a black box producing rows, and historically estimated a fixed row count, which produced poor plans when the real count was very different. SQL Server 2017 and later can interleave execution to get a real count first, which helps but does not make it free.
• Prefer the inline form when one SELECT can do the job. Reach for the multi-statement form when it genuinely cannot, and measure the result.