Microsoft SQL Server 2012 Bible

Scalar Functions

A scalar function is one that returns a single, specific value. The function can accept multiple parameters, perform a calculation, and then return a single value. For example, a scalar function could accept three parameters, perform a calculation, and return the answer.

Within the code of a scalar function, the value is passed back through the function by means of a RETURN command. Every possible codepath in the user-defined function should conclude with a RETURN command.

Scalar user-defined functions may be used within any expressions in SQL Server, even expressions within check constraints (although this isn't recommended practice).

Understanding Limitations

The scalar function must be deterministic, meaning it must repeatedly return the same value for the same input parameters. For this reason, nondeterministic functions — such as newid() and rand() — are not allowed within scalar functions. User-defined scalar functions are not permitted to update the database, call stored procedures, or call DBCC commands, with the single exception that they may update table variables. They cannot return binary large object (BLOB) data such as text, ntext, timestamp, and image data-type variables, nor can scalar functions return table variables or cursor data types. For error handling, UDFs may not include TRY…CATCH or RAISERROR.

A user-defined function may call other user-defined functions nesting up to 32 levels deep, or it can call itself recursively up to 32 levels deep ...