Advanced T-SQL (part 5): Functions
A user-defined function is a Transact-SQL or common language runtime (CLR) routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a value. The return value can be a scalar (single) value or a set of values or a table.
Besides that, in SQL Server, there are also several built-in functions as well.
Scalar function
An SQL scalar function is a user-defined function written in SQL and it returns a single value each time it is invoked.
Syntax
Example
Be careful here, it’s “RETURNS” with a “S”, not just “RETURN”.
Table-valued function (TVF)
It is a user-defined function that returns a table data type and also it can accept parameters. TVFs can be used after the FROM clause in the SELECT statements so that we can use them just like a table in the queries. The TVFs can be categorized into two types. These are inline and multi-statement table-valued functions.
