- Both stored procedures and functions are database objects that contain a set of SQL statements to complete a task.
- A stored procedure can be reused many times. So if you have an SQL query that you intend to use multiple times then save it as a stored procedure, then just call it to execute your SQL query. Additionally, you can also pass parameters to a stored procedure …
- A function is compiled and executed every time the function is called. The function must return a value …
To compare the difference between Stored Procedure and Function, you must have a fairly good knowledge of SQL.
2. The basic difference between Stored Procedure and Function
- The stored procedure may return zero, one or more values. While the function must return a unique value (possibly a table).
- Functions can only have input parameters for it, while a stored procedure can have input or output parameters.
- Functions can be called from stored procedures while stored procedures cannot be called from functions.
3. Advanced differences between Stored Procedure and Function
- Procedures cannot be used in the SELECT statement while the function can be embedded in the SELECT statement. Because a procedure can return multiple result sets, it is not suitable for use in the SELECT statement.
- Stored procedures cannot be used in WHERE / HAVING / SELECT statements while functions can.
- An exception can be handled by try-catch in the stored procedure, which is not possible with the function.
- Transactions can be used in stored procedures, with functions not possible.