User-defined functions (UDFs) are similar to stored procedures, except that they do not support OUTPUT parameters. Instead, a user-defined function returns a value. The type of value returned depends on the type of function. One of the two most notable differences between stored procedures and user-defined functions is that user-defined functions can be used in the SELECT statement, and you can join them to tables, views, CTE and even other functions. The second difference is that you can perform DML operations within stored procedures, but you cannot perform DML operations within user-defined functions.

We primarily use functions to perform logic and complex functions. SQL Server supports Transact-SQL and CLR user-defined functions. The difference between the two is that a Transact-SQL user-defined function is based on Transact-SQL statements and a CLR user-defined function is based on a registered assembly method. In general, CLR user-defined functions...