Difference between Stored Procedure and Function
Table of Content:
Difference between Stored Procedure and Function
Stored Procedures:
Stored Procedures is pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called.
Function:
Function is not pre-compiled object it will execute every time whenever it was called.
Difference between Stored Procedure and Function
| Stored Procedures | Function | |
| Basic Difference | ||
| 1 | Stored Procedure it is optional (Procedure can return zero or n values) | Function must return single value |
| 2 | Stored Procedures can have input/output parameters | Functions can have only input parameters |
| 3 | Functions can be called from Stored Procedure | Stored Procedures cannot be called from Function |
| Advance Difference | ||
| 4 | Exception can be handled by try-catch block in a stored procedure | Exception try-catch block cannot be used in a function |
| 5 |
StoredProcedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it |
Function allows only SELECT statement in it |
| 6 | Stored Procedures cannot be utilized in a select statement | Function can be embedded in a select statement |
| 7 |
Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section |
Function can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section |
| 8 | We can go for Transaction Management in Stored Procedure. | We cannot go for Transaction Management in Function |
| 9 | Stored Procedure can fire Triggers | Function can not fire Triggers. |
| 10 | Stored Procedure can create Table variable but can not return table variable | Function can create Table variable and return table variable. |
| 11 | Print command can be use in stored procedure | Function can not be use print command. |
| 12 | Store procedure can execute Dynamic SQL | Function cant not be execute Dynamic SQL |
| 13 | DML and DDL operation can be performed in SP |
DML and DDL operatoin can be performed in function but we can use only select statement and Insert statement to insert row into table variable |
| 14 | Temporary table can be accessed in stored procedure. Temporary Table can be created in SP | Temporary Table Can not be accessed in Function. We can not create Temporary in Function |