in this topic we will look into one most common question in interview that is, what are difference between Stored Procedure vs Function. Let’s see the below points for these differences.
What are difference between Stored Procedure vs Function?
Stored Procedure
- Stored Procedure return value is optional. Even a procedure can return zero or n values.
- SP can have input or output parameters.
- It cannot be called from a Function.
- It allows to use DQL and DML operation.
- Stored Procedures cannot be used in the DQL statements.
- Stored Procedures capable to handle exception by using try catch block.
- We can use transaction in SP.
Function
The function must return a value.
Functions can have only input parameters.
Functions can be called from Procedure.
It only allows to use DQL statement.
Function can be used anywhere (such as on select, where having) in the DQL statements.
Function unable to handle exception.
we can’t use Transactions in Function.
Better to read the previous topic to get good understanding on the above points.
JOIN in SQL
Stored Procedure in SQL
Function in SQL
Reference Link
Function in SQL Server
