Difference between stored procedures and user defined functions
Solution 1:
This is what i always keep in mind :)
- Procedure can return zero or n values whereas function can return one value which is mandatory.
- Procedures can have input/output parameters for it whereas functions can have only input parameters.
- Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
- Functions can be called from procedure whereas procedures cannot be called from function.
- Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
- We can go for transaction management in procedure whereas we can't go in function.
- Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.
- UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
- UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
- Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
Source http://www.codeproject.com/Tips/286539/Difference-between-stored-procedure-and-function
Solution 2:
A function always returns a value, and can not perform DML statements (INSERT/UPDATE/DELETE).
A stored procedure can not return a value - you need to use an OUT parameter - and can run DML statements.
Advantage of Using a Function vs a Stored Procedure?
Aside from the comparison above, they are equal. But given the comparison, depending on what you need to do it's likely you will use a stored procedure more often than you will a function.
Solution 3:
User defined function has few limitiations like DML statments canbe used etc pls check