Tuesday, October 14, 2008

Difference between Stored Proc and User-defined Functions

Today morning my close friend Prashanth (DotNet expert) asked difference between Stored Proc and Functions . So I am dedicating this post to him, also today is bithday of beautiful Buddu, his girlfriend.

Difference betweem Stored Proc and User-defined Functions

  • Stored Procedure have pre-compiled execuction plan where as functions do not.
  • Functions are used for computations where as procedures are mainly used for performing business logic.
  • Functions can only have 'in' parameter where as SP can have both 'in' and 'out' parameters.
  • Function does not allow DML (insert, update, delete) queries on an object where as SP allows.
  • Functions can be used inline where as SP can't be.
  • Stored Procedure can retun more than one value at a time while funtion returns only one value at a time.
  • Functions MUST return a value, procedures need not be.
Please leave your comments..


Prashanth said...

Thanks Dude thanks very munch

Prashanth said...

It was very gud explanation for what i have asked . There are really important points mentioned out there for interviews

Anonymous said...

really good

arun said...

The second statement on the below page shows functions are also precompiled.


Rahul Kumar said...

Yeap both are pre-compiled, but my point(2) is about Execution Plan.

Post a Comment