STORE PROCEDURE FUNCTION (USER DEFINED FUNCTION)
* Procedure can return 0, single or | * Function can return only single value
multiple values. |
|
* Procedure can have input, output | * Function can have only input
parameters. | parameters.
|
* Procedure cannot be called from | * Functions can be called from
function. | procedure.
|
* Procedure allows select as well as | * Function allows only select statement
DML statement in it. | in it.
|
* Exception can be handled by | * Try-catch block cannot be used in a
try-catch block in a procedure. | function.
|
* We can go for transaction management| * We can not go for transaction
in procedure. | management in function.
|
* Procedure cannot be utilized in a | * Function can be embedded in a select
select statement | statement.
|
* Procedure can affect the state | * Function can not affect the state
of database means it can perform | of database means it can not
CRUD operation on database. | perform CRUD operation on
| database.
|
* Procedure can use temporary tables. | * Function can not use
| temporary tables.
|
* Procedure can alter the server | * Function can not alter the
environment parameters. | environment parameters.
|
* Procedure can use when we want | * Function can use when we want
instead is to group a possibly- | to compute and return a value
complex set of SQL statements. | for use in other SQL
| statements.
| STORE PROCEDURE | FUNCTION (USER DEFINED FUNCTION) |
|---|
| Procedure can return 0, single or multiple values | Function can return only single value |
| Procedure can have input, output parameters | Function can have only input parameters |
| Procedure cannot be called from a function | Functions can be called from procedure |
| Procedure allows select as well as DML statement in it | Function allows only select statement in it |
| Exception can be handled by try-catch block in a procedure | Try-catch block cannot be used in a function |
| We can go for transaction management in procedure | We can not go for transaction management in function |
| Procedure cannot be utilized in a select statement | Function can be embedded in a select statement |
| Procedure can affect the state of database means it can perform CRUD operation on database | Function can not affect the state of database means it can not perform CRUD operation on database |
| Procedure can use temporary tables | Function can not use temporary tables |
| Procedure can alter the server environment parameters | Function can not alter the environment parameters |
| Procedure can use when we want instead is to group a possibly- complex set of SQL statements | Function can use when we want to compute and return a value for use in other SQL statements |
