You can create a multi-statement table-valued function (MSTVF) in SQL Server using the T-SQL CREATE FUNCTION syntax.

Syntax

Here’s the official syntax for multi-statement TVFs.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name   
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type   
    [ = default ] [READONLY] }   
    [ ,...n ]  
  ]  
)  
RETURNS @return_variable TABLE <table_type_definition>  
    [ WITH  [ ,...n ] ]  
    [ AS ]  
    BEGIN   
        function_body   
        RETURN  
    END  
[ ; ]

Example 1 – Basic MSTVF

Here’s an example of a multi-statement table-valued function.

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

The return table’s structure is defined at the start when I specify the @pets variable. The query results are inserted into the @pets variable.

In this case, the function requires a pet name be passed in as an argument. It then uses this argument in the queries in order to return the relevant data. Being a multi-statement table-valued function, I can include multiple statements in the function’s definition.

Example 2 – Add Schema Binding

It’s usually a good idea to schema bind your functions by using the SCHEMABINDING argument.

Doing this will ensure that the underlying tables can’t be changed in a way that would affect your function.

Without schema binding, the underlying tables could be modified or even deleted. Doing this could break the function.

Here’s the same function, but this time with schema binding:

CREATE FUNCTION dbo.udf_PetsByName_MSTVF( @PetName varchar(70))
    RETURNS @pets TABLE (
        PetId varchar(20),
        PetName varchar(70)
    )
    WITH SCHEMABINDING
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

Notice I used two-part names when referencing the tables in my query (I used dbo.Cats and dbo.Dogs when referencing the table, instead of just Cats or Dogs). Doing this is a requirement for schema binding an object. If you try to schema bind an object without using two-part names you’ll get an error.

Now that I’ve schema bound my function, if I try to drop the table referenced in its definition, I get an error:

DROP TABLE Dogs;

Result:

Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE 'Dogs' because it is being referenced by object 'udf_PetsByName_MSTVF'.

By the way, here’s what happens if I try to create the function without using two-part naming:

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

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

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

    RETURN;
END;

GO

Result:

Msg 4512, Level 16, State 3, Procedure udf_PetsByName_MSTVF, Line 10
Cannot schema bind table valued function 'dbo.udf_PetsByName_MSTVF' because name 'Cats' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Example 3 – Add Encryption

You can also encrypt your functions using the ENCRYPTION argument.

Here’s an example of encrypting the function:

CREATE FUNCTION dbo.udf_PetsByName_MSTVF( @PetName varchar(70))
    RETURNS @pets TABLE (
        PetId varchar(20),
        PetName varchar(70)
    )
    WITH SCHEMABINDING, ENCRYPTION
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

Now I’m unable to view the function’s definition.

SELECT definition 
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('udf_PetsByName_MSTVF');

Result:

+--------------+
| definition   |
|--------------|
| NULL         |
+--------------+

I also get an error message when trying to script the function’s definition via Azure Data Studio:

No script was returned when scripting as Create on object UserDefinedFunction

Note that an encrypted function’s text is still available to privileged users that can either access system tables over the DAC port or directly access database files. Also, users that can attach a debugger to the server process can retrieve the original procedure from memory at runtime.