Friday 21 February 2014

Stored Procedure Vs User Defined Function


I think many of us are not aware of the exact differences between a user defined function and a procedure. So, i would like to share some of the basic differences between a function and a stored procedure from my understanding.

Stored Procedures:
1. Procedures can return more than one value.
2. Can have both input and output parameters.
3. Allow all types of sql statements irrespective of reading(SELECT statement) or writing( DML statements)     statements.
4. A procedure can call a function and a procedure can call another procedure.
5. Exception handling can be done in procedures.
6. Transaction management is allowed.
7. Procedures cannot be used in SELECT or HAVING or WHERE clauses.
8. Used to execute Business Logic.
9. Has a pre-compiled execution plan.
10. Can be written to do one or more tasks.
11. A procedure can return maximum of 1024 values.

User-defined Functions:
1. Functions can return one value which is mandatory.
2. Can only have input parameters.
3. Allow only SELECT  queries or reading statements to be written in them.
4. A function cannot call a procedure.
5. Exception handling cannot be done in functions.
6. Transaction management is not allowed.
7. Functions can be used in SELECT or HAVING or WHERE clauses.
8. Functions that return tables can be treated as another row-set. This can be used in join with other tables.
9. Inline functions can be thought of as views that take parameters and can be used in joining it with another row-set.
10. Used for Computations.
11. Not compiled before hand (pre-compiled).

No comments:

Post a Comment

GROUPBY VS PARTITIONBY