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.