In this article, we are going to discuss the differences between function and stored procedure.
Both stored procedure and function contain the set of SQL statements that return the expected result sets. We can use both in different scenarios as per the requirements.
Difference between function and stored procedure
1. Function must return the value but in the stored procedure it is optional, it’s not required in a stored procedure
2. Function we can use only Input parameter, can’t use output parameter but in a stored procedure, we can use both input and output parameters
3. In Function we can use only SELECT command but in Stored procedure, we can use select plus DML(Insert, Update, Delete) commands
4. We cannot use stored procedure in function but we can use the function in the stored procedure
5. In function, Try catch block is not allowed but in a stored procedure, we can use a try-catch block to handle the exception
6. In function, Transaction is not possible, but we can add a transaction in the Stored procedure
7. We can add a function in the SELECT statement but it’s not possible in the stored procedure. We can’t add stored procedure in Select Statement
8. Function return result set, like a table so we can add joins on that but it’s not achievable in the stored procedure
9. We can’t use TEMP table (with a # sign) in function but we can use the in store procedure