Table, Row valued UDFs in SQL Server

SQL Server (and some other databases) support a Table-value function. Is there a such thing as a Row-valued function — where based on a set of input values, a specific row would be returned, or is that type of function type not supported. If not, why isn’t it supported?

The example SQL Server gives is:

CREATE FUNCTION ProductsCostingMoreThan(@cost money)  
RETURNS TABLE  
AS  
RETURN  
    SELECT ProductID, UnitPrice  
    FROM Products  
    WHERE UnitPrice > @cost  

So a Row valued function might look like (I suppose):

CREATE FUNCTION GetCalculatedRow(@pk)
RETURNS ROW
AS
RETURN
    SELECT * FROM Products
    WHERE id=@pk

Maybe it makes no sense (as it’d be easy to have different row-shapes), but I was just curious if this type of thing is supported? I suppose it’s the equivalent of a correlated subquery, so perhaps it might be useful (or at least convenient).

Answer

There are two types of functions in SQL Server:

• Scalar functions that return a single value (i.e. “scalar”)
• Table valued functions that return multiple rows or multiple values.

What you are describing is simply a variant of table-valued functions that return exactly one row (or perhaps no rows, which is possible in your example).

You can call such a function as:

select . . .
from t outer apply
     dbo.rowfunc( . . . )

Or:

select *
from t left join
     dbo.rowfunc ( . . . )
     on 1=1

Or:

select *
from db.rowfunc( . . . )

The first two return values even when no row is returned. The third doesn’t have that possibility (easily).

Note that some databases support “tuples” which is (I am guessing) what you mean by “row” — multiple columns in a single “scalar” value. SQL Server is not one of them.

However, you can emulate tuples using JSON or XML, if you want to return multiple values.