Chapter 10. User-Defined Functions
Great designers have a relentless desire to createto make things.
Steve McConnell[1]
[1] McConnell, Steve. After the Gold Rush. Redmond, WA:Microsoft Press, 1999.
A UDF is a special type of subroutine that can be used in expressions. UDFs are very much like Transact-SQL's built-in functionsyou pass them the parameters they require and they return a result of some type.
UDFs come in three varieties: scalar functions, multistatement table-valued functions, and inline table-valued functions. A scalar function is one that returns a single value. It can be used in expressions just like regular system functions. A multistatement table-valued function (what I'll usually just refer to as a table-valued function) is a UDF that returns a table data type as its result. You can use it in the FROM clause of a SELECT statement similarly to Transact-SQL's built-in rowset functions (e.g., OPENXML(), OPENQUERY(), and so on). An inline table-valued function (what I'll usually refer to as an inline function) returns the results of a SELECT statement as its resultit has no function body. It can also be used in the FROM clause of the SELECT statement.