Store Procedures are
pre-compile 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.
Basic Difference
1. Function must return a value but in Stored
Procedure it is optional( Procedure can return zero or n values).
2. Functions can have only input parameters for
it whereas Procedures can have input/output parameters .
3. Function takes one input parameter it is
mandatory but Stored Procedure may take o to n input parameters..
4. Functions can be called from Procedure whereas
Procedures cannot be called from Function.
Advance Difference
1. Procedure allows SELECT as well as
DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT
statement in it.
2. Procedures can not be utilized in a SELECT
statement whereas Function can be embedded in a SELECT statement.
3. Stored Procedures cannot be used in the SQL
statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
4. Functions that return tables can be treated as
another rowset. This can be used in JOINs with other tables.
5. Inline Function can be though of as views that
take parameters and can be used in JOINs and other Rowset operations.
6. Exception can be handled by try-catch block in
a Procedure whereas try-catch block cannot be used in a Function.
7. We can go for Transaction Management in
Procedure whereas we can't go in Function.
No comments:
Post a Comment