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.
Thanks Dude thanks very munch
ReplyDeleteIt was very gud explanation for what i have asked . There are really important points mentioned out there for interviews
ReplyDeletereally good
ReplyDeleteThe second statement on the below page shows functions are also precompiled.
ReplyDeletehttp://msdn.microsoft.com/en-us/library/6z6w48f5.aspx
@Arun,
ReplyDeleteYeap both are pre-compiled, but my point(2) is about Execution Plan.