*
The moose likes JDBC and the fly likes Difference between Stored Procedures and Functions Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Difference between Stored Procedures and Functions" Watch "Difference between Stored Procedures and Functions" New topic
Author

Difference between Stored Procedures and Functions

Vineela Devi
Ranch Hand

Joined: Dec 20, 2003
Posts: 191
Hi,

Can anyone pls explain me the diff between StoredProcedures and Functions?

Regards,
Vineela
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

Functions are supposed to be utility kind of code which return some value whereas procedures can have side effects i.e. it changes some value in the database. SP can have IN/OUT parameters which means that more than one return value is possible.


Groovy
Prem Kumar
Greenhorn

Joined: Jan 29, 2006
Posts: 14
A Procedure may or may not return a value.By default it wont return any thing.You have to make it return some value by the use of OUT or IN/OUT parameter.

Function will return a value by default.


Have a Great Day!
Vineela Devi
Ranch Hand

Joined: Dec 20, 2003
Posts: 191
Hi,
Thanks for ur replies.Can u please tell me in detail when should we optfor functions and when for stored procedures?

Regards,
Vineela
Anonymous
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
Totally depends on you, wanting sth returned or not.

Say in one case, I had to ignore any exception returned, and in case of any just would inform the end user by getting "-1" as return from a function.

Actually from the point of the database they (proc. and fnctn) are no different.
Nilesh Molankar
Greenhorn

Joined: Nov 13, 2005
Posts: 3
Hi,
Basically a stored proc can do everything that a function can but the opposite need not be true always.
Like you cannot change the system date etc from a UDF but you can do it from a Stored Proc.

--Nilesh
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Difference between Stored Procedures and Functions