What is the difference between Stored Procedure and User defined funtion
By Anoop S
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
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
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
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.
What is the difference between Stored Procedure and User defined funtion (1749 Views)