Sql server User Defined Function (UDF) VS Stored Procedure (SP)

UDFs vs. Stored Procedures
UDFs and stored procedures are both SQL Server objects that store one or more T-SQL statements in a single named, executable routine. Although you can often implement the same or similar functionality using either a UDF or a stored procedure, the code will look significantly different depending on which technique you choose. Here are the main differences between UDFs and stored procedures:

• A UDF must return a value-a single result set. A stored procedure can return a value-or even multiple result sets-but doesn’t have to.
• You can use a UDF directly in a SELECT statement as well as in ORDER BY, WHERE, and FROM clauses, but you can’t use a stored procedure in a SELECT statement.
• A UDF can’t use a nondeterministic function such as GETDATE(), NEWID(), or RAND(), whereas a stored procedure can use such functions. A nondeterministic function is one that can return a different result given the same input parameters.
• A UDF can’t change server environment variables; a stored procedure can.
• A UDF always stops execution of T-SQL code when an error occurs, whereas a stored procedure continues to the next instruction if you’ve used proper error handling code.

Reference