Function, in computer programming language context, a set of instructions which takes some input and performs certain tasks. In SQL, a function returns a value.
Procedure, as well, is a set of instructions which takes input and performs certain task. In SQL, procedure does not return a value. In java, procedure and functions are same and also called sub-routines.
Following are the important differences between SQL Function and SQL Procedure.
|1||Definition||A function is used to calculate result using given inputs.||A procedure is used to perform certain task in order.|
|2||Call||A function can be called by a procedure.||A procedure cannot be called by a function.|
|3||DML||DML statments cannot be executed within a function.||DML statements can be executed within a procedure.|
|4||SQL, Query||A function can be called within a query.||A procedure cannot be called within a query.|
|5||SQL, Call||Whenever a function is called, it is first compiled before being called.||A procedure is compiled once and can be called multiple times without being compiled.|
|6||SQL, Return||A function returns a value and control to calling function or code.||A procedure returns the control but not any value to calling function or code.|
|7||try-catch||A function has no support for try-catch||A procedure has support for try-catch blocks.|
|8||SELECT||A select statement can have a function call.||A select statemnt can't have a procedure call.|
|9||Explicit Transaction Handling||A function can not have explicit transaction handling.||A procedure can use explicit transaction handling.|