Different between User Define Function and Stored Procedure

1. Stored proc is precompiled while this is not case in function.
2. we cant use DML operations in functions where as it is possible in sp.
3. functions always return one value but sp may or may not return .
4. error handling can be done in sp, but not possible in function.
5. functions can be called from select statements, where clause and case but not possible in sp.

Stored Procedure :supports deffered name resoultion Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws errorFunction wont support deffered name resolution.
2. Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values(SQL Server).
3. Stored Procedure is pre compiled exuction plan where as functions are not.
4. Stored Procedure retuns more than one value at a time while funtion returns only one value at a time.
5. We can call the functions in sql statements (select max(sal) from emp). where as sp is not so
6. Function do not return the images,text whereas sp returns all.
7. Function and sp both can return the values. But function returns 1 value only.
procedure can return multiple values(max. 1024) we can select the fields from function. in the case of procdure we cannot select the fields.
8. Functions are used for computations where as procedures can be used for performing business logic
9. Functions MUST return a value, procedures need not be.
10. You can have DML(insert, update, delete) statements in a function. But, you cannot call such a function in a SQL query..eg: suppose, if u have a function that is updating a table.. you can't call that function in any sql query.
- select myFunction(field) from sometable;
will throw error.
11. Function parameters are always IN, no OUT is possible