Stored Procedures are precompiled objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. But Function is compiled and executed every time when it is called. Functions and Stored Procedures serve different purposes.
- Function must return one value which is mandatory, but in Stored Procedure it is optional (Procedure can return zero, one or multiple values).
- Functions can have only input parameters, whereas Procedures can have input/output parameters.
- Functions can be called from Procedure, but Procedures cannot be called from Function.
- Procedure allows SELECT as well as DML (INSERT/UPDATE/DELETE) statement in it, but Function will allow only SELECT statements, it will not allow us to use DML statements
- Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
- Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
- Functions can be used in JOIN clause as a result set, Procedures can’t be used in JOIN clause.
- Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
- Exception can be handled by try-catch block in a Procedure, but try-catch block cannot be used in a Function.
- Transactions are not allowed within functions, but we can use transactions within Stored Procedures.
- User Defined Functions in SQL are similar to mathematical functions or functions in any programming language.
- Stored Procedures are a batch of SQL statements, which may apply CRUD operations to data in DB.
- There are many differences between two, UDF ans SP have different areas of application.