Introduction to Multi-Statement Table-Valued Functions (MSTVF) in SQL Server

In SQL Server, the multi-statement table-valued function is one of two types of Transact-SQL table-valued functions (the other type being the inline table-valued function).

Table-valued functions (TVF) are a type of user-defined function that return their results as a table. They can therefore be queried just like a normal table.

Multi-statement TVFs (sometimes referred to as MSTVFs) can consist of multiple statements, the results of which are stored in a return variable. You include the specifications of the return variable at the top of the function. This specifies the structure of the return table. In other words, you specify how many columns, their names, data types, etc.

This is in contrast to inline TVFs (also referred to as ITVFs), which do not use a return variable (the return table is defined by the SELECT statement).

MSTVFs also use the BEGIN/END syntax, which is another thing that distinguishes them from ITVFs (ITVFs don’t use that syntax).

Example of a Multi-Statement Table-Valued Function

Here’s an example of a basic MSTVF:

CREATE FUNCTION dbo.udf_PetsByName_MSTVF( @PetName varchar(70))
    RETURNS @pets TABLE (
        PetId varchar(20),
        PetName varchar(70)
    )
AS
BEGIN
    INSERT INTO @pets
    SELECT 
        CONCAT('Cat', ' ', CatId),
        CatName
    FROM dbo.Cats
    WHERE CatName = @PetName;

    INSERT INTO @pets
    SELECT 
        CONCAT('Dog', ' ', DogId),
        DogName
    FROM dbo.Dogs
    WHERE DogName = @PetName;

    IF @@ROWCOUNT = 0
    BEGIN
        INSERT INTO @pets
        VALUES (
            '',
            'There are no pets of that name.'
            )
    END

    RETURN;
END;

GO

Here, I start the function by defining the return variable called @pets. It’s of type table, and it will return two columns.

In this case I have two SELECT statements and an IF statement. The results of each one is stored in the return variable. This is done via an INSERT statement each time.

Function Options

You can also specify things like, whether or not to use schema binding (you probably should), and whether or not to encrypt the function.

Schema binding will prevent any adverse changes being done to the underlying objects that the function depends on (such as dropping a table, altering a column, etc).

Encryption will convert the function’s definition to an obfuscated format (to prevent others from being able to read it).

See Create a Multi-Valued Table-Valued Function for examples of adding schema binding and encryption to an ITVF.