Table-Valued Function (TVF) Explained (with Examples) - T-SQL

Table Valued Function (TVF) in T-SQL (Transact-SQL) returns a table.  Especially this feature is useful with Entity Framework because EF exposes this table class, whereas the Stored Procedure requires a manual extension to the database context by creating an additional class that Stored Procedure returns so that your .NET code can consume it when you call the Stored Procedure.
 

728x90

There are two types of TVF. 

---

1.  Inline Table-Valued Function

It returns a table from one SELECT block.  You cannot insert any intermediate logic before returning a table.

2. Multi-statement Table-Valued Function

You can insert intermediate logic before returing a table.  This is very similar to Stored Procedure.
 

반응형

---

INLINE TABLE-VALUED FUNCTION EXPLAINED

CREATE FUNCTION udfProductInYear (
    @model_year INT  -- PARAMETER
)
RETURNS TABLE
AS
RETURN
    SELECT 
        product_name,
        model_year,
        list_price
    FROM
        production.products
    WHERE
        model_year = @model_year;

The example above describes an Inline TVF.  As you see there is no intermediate logic and you are immediately returning a table using a SELECT code block.  This is useful when you have special JOINs, WHEREs, and / or SELECTs only certain columns, that you can write in ONE SELECT clause.

---

MULTI-STATEMENT TABLE-VALUED FUNCTION EXPLAINED

CREATE FUNCTION udfContacts()
    -- DEFINES A RETURN TABLE STRUCTURE
    RETURNS @contacts TABLE (
        first_name VARCHAR(50),
        last_name VARCHAR(50),
        email VARCHAR(255),
        phone VARCHAR(25),
        contact_type VARCHAR(20) 
    )
AS
BEGIN
    -- INSERT STAFF DATA TO THE TABLE TO RETURN
    INSERT INTO @contacts
    SELECT 
        first_name, 
        last_name, 
        email, 
        phone,
        'Staff'
    FROM
        sales.staffs;

    -- INSERT CUSTOMER DATA TO THE TABLE TO RETURN
    INSERT INTO @contacts
    SELECT 
        first_name, 
        last_name, 
        email, 
        phone,
        'Customer'
    FROM
        sales.customers;
    RETURN;
END;

The "crude" and "simple" example above describes a case where you add intermediate logic to manipulat data BEFORE returning the table.  It will add one type of record "staff" and then another type "customer" to the table.  Then the returned table will have both types in one table.  This is an example of Multi-Statement TVF (you see it is similar to a stored procedure).  In addition to intermediate logic, another difference is that you must define a table structure in the beginning of the function.
 
As you can tell, this can also be written in Inline TVF using UNION.

---