1. Table valued UDFs
Now that you know how to return a single scalar value from a UDF, it's time to return a table result.
2. Inline table valued functions (ITVF)
There are two different kinds of table valued functions, inline and multi statement. Let's review the code for an inline table valued function, or ITVF.
The first line is similar to a scalar function with the CREATE FUNCTION keywords, followed by the function name, then the input parameter name and data type. One difference here is the parameter has a default value of 1/1/2017. Assigning default values to parameters is an option in all user defined functions. The next line contains the RETURNS keyword, but TABLE follows instead of a scalar data type. The RETURN keyword is followed by the SELECT statement. There is no BEGIN END block necessary because SQL Server returns the results of the single SELECT statement. Scalar functions require the use of the BEGIN END block, regardless of whether it's a single statement, but a table valued function doesn't require BEGIN END if the function body is a single statement. Column names need to be assigned in the SELECT statement because a table is being returned.
This function returns a table containing the ride count and total trip distance for each PickupLocation where the transaction's StartDate is equal to the parameter value passed.
3. Multi statement table valued function (MSTVF)
Now let's review a multi statement table valued function, or MSTVF. Again the first line is similar to the INLINE and SCALAR with the CREATE FUNCTION keywords, function name, and input parameter definitions. The RETURNS keyword on the second line is followed by a TABLE variable definition, including column names and data types for each. Since this is a multi statement, we need to use a BEGIN END block to contain the multiple SQL statements. We are returning the table variable, so we need to INSERT the SELECT statement results. After the INSERT and SELECT, we need to tell the function to RETURN the table variable. RETURN is the last statement within the BEGIN END block.
Don't forget to assign the correct data type to the table variable columns. They need to align with the data that will be inserted.
4. Differences - ITVF vs. MSTVF
What are the main differences between the Inline and Multi Statement Table Valued Functions? Inline returns the results of a SELECT statement along with the assigned column names. It doesn't contain a table variable, BEGIN END block, or INSERT. It's probably not surprising that it usually performs faster compared to a multi statement. This should always be your preferred route. If you can write your query in a single statement, you should.
A Multi Statement table valued function must DECLARE a table variable to be returned, contain a BEGIN END block, INSERT data into the table variable and RETURN should be the last statement within the BEGIN END block. This type of table valued function should only be used when you cannot write the query in a single statement.
5. Your turn!
Let's create some table valued functions.