What is the difference between Stored Procedure and User defined funtion

The user defined function provides a mechanism for extending the functionality of the database server by adding a function that can be evaluated in SQL statements. A stored procedure is a subroutine available to applications accessing a relational database system

1. Procedures may or may not return a value or may return more than one value using the OUTPUT and/or INOUT parameters but user defined function always returns only one value.

2. User defined function may called from Stored procedure but User Defined Function can’t call a Stored Procedure from inside itself.

3. Stored Procedure has input or output parameter but User defined function has only input parameters

4. Stored procedure can be called independently using Execute/ exec keyword.  Functions are called from select/where/having clause.

5. Exception handling done in Stored Procedure done by Try- Catch block but it can’t use in User defined function.

6. Transaction management possible in Stored Procedure but not possible in User defined function.

7. Stored procedures are stored in database in the compiled form. Function are parsed and compiled at runtime only.

8. Stored procedures can create a table but can’t return table. Functions can create, update and delete the table variable. It can return a table also.