User Defined Functions Vs Stored Procedures

Most databases have built-in functions, such as GetDate, DateAdd, and ObjectName. Such built-in functions are useful, but you can’t alter their functionality in any way, and that’s why UDFs are so powerful and necessary. UDFs allow you to add custom solutions for unique application-specific problems.
A UDF is actually a kind of subroutine that contains T-SQL statements and can return a scalar value or a table value. Hence U can call a UDF from a SELECT statement. Whereas a Stored Procedure needs to be invoked using the 'EXEC' command.
So when to use what? The answer depends on the problem situation. If you have an operation such as a query with a FROM clause that requires a rowset be drawn from a table or set of tables, then a function will be your appropriate choice. However, when you want to use that same rowset in your application the better choice would be a stored procedure.

The other differences btw a UDF and a Stored Procedure are as follows:

• A stored prodecure supports output parameters, whereas a UDF does not.
• A UDF can have a return value (scalar or table). A SPROC can create a table, but cannot return it. It can only return a integer (as status code)
• SPROCs are powerful enough to change server environment variables. UDFs are not.
• SPROCs can be used in the FOR XML clause but UDFs can't be.
• Whenever an error occurs in a UDF, the function stops immediately. In a SPROC, we must include error handling code, or the next statement is processed.
• U can 'join' to a UDF, but cannot 'join' to a SPROC.