Store Procedure and Function
| Function | Procedure |
|---|---|
| The function is compiled every timeit is called for execution. | Procedures are pre-compiled and saved. They execute the pre-compiled code whenever called. |
| Can be called from SQL statements. | Can not be called from SQL statements. |
| The function has to return a value. | Need not return any value. |
| Generally used for computation purpose. | Used for executing complex business logic. |
| Can return multiple values using other methods, otherwise, return only a single value. | Can return multiple values |
| Returns scalar data types. | Returns an int by default. |
| A stored procedure can not be called from a function | The procedure can call any function |
| Functions can be embedded in a select statement | Inside a select statement, a procedure cannot be called. |
| Exception handling is not possible | Try/catch block can be defined inside a procedure |
Answer:
| PROCEDURE | TRIGGER |
|---|---|
| Called explicitly by a user, trigger or an application | Executed by the DBMS whenever an event occurs in the database. |
| Can have parameters | Doesn’t have parameters |
| Cannot be inactive | Can be enabled or disabled on need basis |
| Creation – CREATE PROCEDURE | Creation – CREATE TRIGGER |
