Microsoft SQL Server 2012 Bible

Multistatement Table-Valued Functions

The multistatement table-valued, user-defined function combines the scalar function's capability to contain complex code with the inline table-valued function's capability to return a result set. This type of function creates a table variable and then populates it within code. The table is then passed back from the function so that it may be used within SELECT statements. From a query optimizer standpoint, it is treated much like an external call, like joining a stored procedure's result set to a table using OPENQUERY.

The primary benefit of the multistatement table-valued, user-defined function is that complex result sets may be generated within code and then easily used with a SELECT statement. This enables you to build complex logic into a query and solve problems that would otherwise be difficult to solve without a cursor.

The APPLY command may be used with multistatement table-valued, user-defined functions in the same way that it's used with inline user-defined functions.

Creating a Multistatement Table-Valued Function

The syntax to create the multistatement table-valued function is similar to that of the scalar user-defined function:

CREATE FUNCTION FunctionName (InputParamenters)
RETURNS @TableName TABLE (Columns)
AS
BEGIN;
 Code to populate table variable
 RETURN;
END;

The following process builds a multistatement table-valued, user-defined function that returns a basic result set:

1. The function first creates a table variable called ...