AW: Difference between SQL Function and Procedure.

Hi Mike,

a stored procedure is a program or a service program object. It can have
input and output parameters and return result sets. 
A stored procedure must be called from a SQL interface (such as embedded SQL
, ODBC, JDBC etc) by using the SQL command CALL.

A user defined function (UDF) is a service program object. It only accepts
input parameters and always have a single return value. All output
information must be returned in the return value. 
A user defined function can be used like any scalar function.
(I would not use am UDF to update a table)

A user defined table function (UDTF) is a service program object, that only
accepts input parameters, but has several return values (table)
A user defined table function can be used in a SQL select statement like a
file (table).

For more information look at one of the following redbooks:
Modernizing IBM eServer iSeries Application Data Access - A Roadmap
Cornerstone 
http://www.redbooks.ibm.com/abstracts/sg246393.html?Open

Stored Procedures, Triggers and User Defined Functions on DB2 Universal
Database for iSeries
http://www.redbooks.ibm.com/abstracts/sg246503.html?Open

Birgitta

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Mike Pantzopoulos -
(H/O)
Gesendet: Donnerstag, 14. Juli 2005 02:38
An: midrange-l@xxxxxxxxxxxx
Betreff: Difference between SQL Function and Procedure.

I'm a little confused between the exact difference between an SQL
function and SQL procedure.

They both seem to be able to chieve what I want - update a single column
in a record in a table, where the record key and update value are passed
in as parameters. 

I presume the difference is that a function can return a value (like HLL
functions), and a Stored Procedure can't.

Is that it? 

Mike Pantzopoulos



****************************************************************************
*****************************
This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.
If you are not the intended recipient, any use, disclosure or copying of
this message is unauthorised. If you have received this message in error,
please reply using the sender's email address. 

This footnote confirms that this email message has been scanned for computer
viruses. EIG-Ansvar Limited does not accept liability for any loss or
damage, whether caused by our own negligence or not, that results from a
computer virus or defect in the transmission of this email or any attached
file. 
EIG-Ansvar Limited - Australia (A.B.N. 21 007 216 506)
Email : insure@xxxxxxxxxxxxxxxx

Eig-Ansvar Limited - New Zealand
Email : insure@xxxxxxxxxxxxxxxx

****************************************************************************
*****************************