Difference between function and storedprocedure
1.Procedure can return zero or n values whereas function can
return one value which is mandatory.
2.Procedures can have input,output parameters
for it whereas functions can have only input parameters.
3.Procedure allow select as well as DML
statement in it whereas function allow only select statement in it.
4.Functions can be called from procedure
whereas procedures cannot be called from function.
5.Exception can be handled by try-catch block
in a procedure whereas try-catch block cannot be used in a function.
6.We can go for transaction management in
procedure whereas we can't go in function.
7.Procedures can not be utilized in a select
statement whereas function can be embedded in a select statement.
8.Functions are normally used for computations where as procedures
are normally used for executing business logic.
9.A Function returns 1
value only. Procedure can return multiple values (max
1024).
10.Stored procedure
returns always integer value by default zero. Whereas function
returns type could be scalar or table or table values
11.Stored procedure is
precompiled execution plan where as functions are not.
12. A function can call
directly by SQL statement like select func_name from dual while procedure cannot.
13.We can go for transaction management in procedure whereas we can't go in function.
14.Function Cannot be used to change server configuration.sp can be used to change server configuration