Function and StoredProcedure

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
Ebook Download
View all
Learn
View all