Both procedures and functions can be described as a group of SQL statements, that perform a specific task. They are usually used to perform a more complex operations. Procedures and functions might look very similar in some cases, but there are several key differences between them:User Defined Function (UDF)
Stored Procedure (SP)
- Is used to get a result using given inputs. It cannot be used to modify the database (statements INSERT, UPDATE or DELETE are not allowed).
- Can have only Input parameter.
- Should return a single value.
- Procedure cannot be called from a function.
- Transactions are not possible.
- Can be used in the SQL statements in the SELECT, WHERE or HAVING section.
- Can be used to modify the database (statements INSERT, UPDATE or DELETE can be used).
- Can have Input and Output parameters.
- Can return zero, single or multiple values.
- Function can be called from a procedure.
- Transactions are possible.
- Cannot be used in the SQL statements in the SELECT, WHERE, or HAVING section.