Differentiate between a Stored Procedure and a User Defined Function

• Stored Procedures can or cannot return values but functions have to return a value which is mandatory.
• Stored Procedures can have input/output parameters but functions only have input parameters.
• Stored Procedures allows execution of SELECT as well as DML statements but functions allows execution of SELECT statement only.
• Stored Procedures cannot be called from functions but functions can be called from Stored Procedures.
• Stored Procedures can handle exceptions by using try-catch block which cannot be used in functions.
• Transactions can be implemented in stored procedures, but not in functions.
• Stored Procedures cannot be used in a SELECT statement whereas functions can be used in a SELECT statement.
• User Defined Functions can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section, but stored procedures cannot.
• Stored Procedures can be used with XML FOR clause but functions cannot be used with XML FOR clause.
• Stored Procedures can create a table, but cannot return it. Functions can return a table.
• Stored Procedures can be called independently using EXECUTE or EXEC keyword but functions are called using SELECT statements.
• User Defined Functions that return tables can be treated as another rowset and can be used in JOINs with other tables. Inline User Defined Functions can be though of as views that take parameters and can be used in JOINs and other Rowset operations.