1. Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT or INOUT parameter.
2. Function can be called from SQL statements where as procedure can not be called from the sql statements
3.Stored Procedure: supports deferred name resolution. 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 error Function wont support deferred name resolution.
4.Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values
5. Stored procedure is precompiled execution plan where as functions are not.
6.A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller.
7.Function can take only input aurguments, but procedure may take both input and out put parameters.
8) 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.
9.There is possible of recurssive functionlike max(min(something)),but there is nothing like in procedure. 10.We can call a function from a procedure, but it is not possible to call a procedure from a function
11.Functions cannot affect the state of the database which means we cannot perform insert,delete,update and create operations
operations on the database.Stored Procedures can affect the state of the database by using insert,delete,update and create operations. 12.Functions are basically used to compute values. We passes some parameters to functions as input and then it performs some operations on the parameter and return output.
Stored procedures are basically used to process the task. 13.Function can not change server environment and our operating system environment. Stored procedures can change server environment and our operating system environment.
