Invoking User-Defined Functions That Return a Table Data Type
You can invoke a user-defined function that returns a table where table expressions are allowed in the FROM clause of SELECT, INSERT, UPDATE, or DELETE statements. An invocation of a user-defined function that returns a table can be followed by an optional table alias. This example illustrates calling a function fn_Products and assigning an alias:
SELECT OD.OrderID, OD.ProductID, fnPr.Price
FROM OrderDetails as OD, fn_Products('Discontinued') AS fnPr
WHERE OD.ProductID = fnPr.ProductID
ORDER BY OD.OrderID, OD.ProductID
When a user-defined function that returns a table is invoked in the FROM clause of a subquery, the function arguments cannot reference any columns from the outer query.
Static, read-only cursors are the only type of cursor that can be opened on a SELECT statement whose FROM clause refers to a user-defined function that returns a table.
A SELECT statement that references a user-defined function that returns a table invokes the function once.