Functions Vs Stored Procedures

09:37 No Comments

It is one of the famous or frequently asked interview question, so I am stating the difference between these two which I think is appropriate, Please correct me if I have left anything unattended here.

1. Basic difference is a procedure can return nth values whereas function can return only one value which is mandatory to be.
2. Function can have only input parameters whereas procedures can have both input/output parameters.
3. Function doesn't allow an update, delete, create(DML Statements) statements into it where as Procedure do.
4. Functions can be called from DML or select statements whereas procedures canonly call from procedure or direct through call statements.
5. We can not do transaction management in function since it doesn't have DML queries and it is prohibited too where as procedure can be used to do transaction management.
6. No Exception handling in Function whereas reciprocate to it Procedures do, means in procedure try catch block can be used to handle exception.
7. Procedures can not be use in a select statement whereas function can be call in a select statement.
8. UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
9. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
10. Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

 Please feel free to contact me for any issues via comments.