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 ...