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.