Differences between Stored Procedures and Functions

Stored Procedures	Function
Use EXEC or EXECUTE	Used with Select statement
Return output parameter	Not returning output parameter but returns Table variables
Cannot join SP	We can join UDF
Can be used to change server configuration.	Cannot be used to change server configuration.
Can be used with XML FOR Clause	Cannot be used with XML FOR clause
Can have transaction within SP	Cannot have transaction within function
SP takes input, output parameters	Function takes only input parameters.
SP cannot be called directly into DML statements	Functions can be called directly into DML statements.
SP may return one or more values through parameters or may not return at all	A FUNCTION is always returns a value using the return statement
SP can return multiple values (max 1024).	A Function returns 1 value only.
SP returns always integer value by default zero.	Whereas function returns type could be scalar or table or table values
SP is precompiled execution plan	A function can call directly by SQL statement like select func_name from dual
SP cannot be used in SQL queries	A Function can be used in the SQL Queries
We can use try catch statements in stored procedures	Functions we can not use try catch statements.
We can use insert,delete,update and create statements in SP.	We can not use insert,delete,update and create statements in functions.
Stored procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section.	UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section