Thursday, May 20, 2010

Difference Between function and Procedure

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. 

6 comments:

rine said...

Hi

I read this post 2 times. It is very useful.

Pls try to keep posting.

Let me show other source that may be good for community.

Source: Operations interview questions

Best regards
Jonathan.

Muthu Ganapathy said...

hi I too like this blog.It was very informative.

kamlesh khaliya said...

hi

i disagree with point 7.

you can create a function with out parameters also.... i am talking about oracle and the version i am working is 10G.

you can try it at your end.

Thanks for the article.

Khaleek said...

Hi Kalesh Khaliya,

I have written all the point according to SQL Server. Anyways thanks for information.

Anonymous said...

Points 8 and 11 appear to be contradictory. which one is correct?
Btw nice summary.

Khaleek said...

Not a contradict but same, you can write DML statement in a function but that function can't be called i.e useless.

Followers

Link