Table Valued Function and Inline Table Valued Function

Inline user defined table valued function is a subset of user defined table valued function. It can be used to achieve the functionality of parameterized views.

Simply, inline table valued function RETURNS TABLE and usually contains only one SELECT statement. Table valued function returns a table data type,   and it can contain additional statements that allow more powerful logic than is possible in views.

Example 1: Inline table valued function

CREATE FUNCTION Sales.ufn_CustomerNamesInRegion
                 ( @Region nvarchar(50) )
RETURNS table
AS
RETURN (
        SELECT DISTINCT s.Name AS Store, a.City
        FROM Sales.Store AS s
        INNER JOIN Person.BusinessEntityAddress AS bea 
            ON bea.BusinessEntityID = s.BusinessEntityID 
        INNER JOIN Person.Address AS a 
            ON a.AddressID = bea.AddressID
        INNER JOIN Person.StateProvince AS sp 
            ON sp.StateProvinceID = a.StateProvinceID
        WHERE sp.Name = @Region
       );
GO

Example 2: Table valued function

In a table-valued user-defined function:

   1.The RETURNS clause defines a local return variable name for the table returned by the function. The RETURNS clause also defines the format of the table. The scope of the local return variable name is local within the function.

   2. The Transact-SQL statements in the function body build and insert rows into the return variable defined by the RETURNS clause.

    3.When a RETURN statement is executed, the rows inserted into the variable are returned as the tabular output of the function. The RETURN statement cannot have an argument.

    4. No Transact-SQL statements in a table-valued function can return a result set directly to a user. The only information the function can return to the user is the table returned by the function.