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..

5 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.

http://msdn.microsoft.com/en-us/library/6z6w48f5.aspx

Rahul Kumar said...

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

Post a Comment