`
SQL-Stored Procedure vs Function
In SQL, both stored procedures and functions are used to perform operations and can be part of your database logic. However, there are key differences between them, particularly when dealing with table operations.
Difference between the STORE PROCEDURE vs FUNCTION
| STORE PROCEDURE | FUNCTION |
|---|---|
| Procedure can return 0, single or multiple values | Function Must return a 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 |
Next