What is the differnce between store procedure and Functions (UDF)

1. What is the difference between stored procedures and function

A procedure or function is an object stored in the database, and run as a unit to solve a specific problem or perform a set of related tasks. Procedures and functions permit the caller to provide parameters that can be input only, output only, or input and output values. Procedures and functions let you combine the ease and flexibility of SQL with the procedural functionality of a structured programming language.

Procedures and functions are identical except that functions always return a single value to the caller, while procedures do not but could return value.
function returns 1 value only.

procedure can return multiple values(max. 1024)

1. A function can have return statement where as a procedure cannot have a return statement.


2. A procedure can be executed separately from SQL and also can be invoked from some other procedures or functions where as a function can only be invoked from a function or a procedure.
   


3. A function can be used in DML where as a procedure cannot be invoked from a DML.
   We cannot use DDL in a function where as in procedure it is possible using dynamic SQL package.
   
4. A procedure can be either stored procedure or an anonymous where as a function cannot be anonymous.
   


5. We cannot use a Transaction control language in a function where as in Procedures we can use TCL.
   


6. Functions can also have IN and OUT parameters.
   

Functions are used for computations where as procedures can be used for performing business logic.

Function :

1. Should return atleast one output parameter.Can return more than one parameter using OUT argument.
   


2. Parsed and compiled at runtime.
   


3. Cannot affect the state of database.
   


4. Can be invoked from SQL statement e.g. SELECT.
   

Functions are mainly used to compute values.

Procedure:

1. Doesn't need to return values, but can return value.
   


2. Stored as a pseudo-code in database i.e. compiled form.
   


3. Can affect the state of database using commit etc. (DDL)
   


4. Cannnot be invoked from SQL statements e.g. SELECT.