Can anyone please explain what is the exact difference between stored procedures and user defined functions and in which context what is useful?
A:
User defined function has few limitiations like DML statments canbe used etc pls check
gkpstar
2010-01-11 04:52:57
+2
A:
A function always returns a value, and can not perform DML statements (INSERT/UPDATE/DELETE).
A stored procedure can not return a value - you need to use an OUT parameter - and can run DML statements.
Advantage of Using a Function vs a Stored Procedure?
Aside from the comparison above, they are equal. But given the comparison, depending on what you need to do it's likely you will use a stored procedure more often than you will a function.
OMG Ponies
2010-01-11 05:02:37
then wat is the advantage of using function in any context???
vasu
2010-01-11 07:12:06
You can embed functions into other statements because they return values. Lets you do things like SELECT * FROM TableFunction(Param1, Param2) TF INNER JOIN Table T ON T.Key=TF.FKey WHERE TF.Col1=ValueFunction(Param3)
eftpotrm
2010-01-14 18:19:31
@eftpotrm: That usage is frowned upon, because you can replace it with a JOIN. Also, it encapsulates the logic - meaning if used in the SELECT portion that you are running a correlated SELECT, which will execute for every row returned. Been there, done that, saw how poorly the query scaled.
OMG Ponies
2010-01-14 18:53:24