RE: Difference between SQL Function and Procedure.

["Wilt, Charles" <CWilt@xxxxxxxxxxxx>]

Mike,

Birgitta covered the technical details quite well.

One thing I want to point out is the difference in usage.

A SQL function could possibly be called for every in a table.  Thus you want 
your functions to be quick little routines.  From the SQL Programming Concepts 
manual:

"UDFs are called from within an SQL statement execution, which is normally a 
query operation that potentially runs against thousands of rows in a table. 
Because of this, the UDF needs to be called from a low level of the database. 
As a consequence of being called from such a low level, there are certain 
resources (locks and seizes) being held at the time the UDF is called and for 
the duration of the UDF execution. These resources are primarily locks on any 
tables and indexes involved in the SQL statement that is calling the UDF. Due 
to these held resources, it is important that the UDF not perform operations 
that may take an extended period of time (minutes or hours). Because of the 
critical nature of holding resources for long periods of time, the database 
only waits for a certain period of time for the UDF to finish. If the UDF does 
not finish in the time allocated, the SQL statement calling the UDF will fail."

SQL procedures are simply SQL Programs; They can take as much time as needed.  
SQL Procedures are called just like a CL program calls a RPG program.  In fact, 
by default every *PGM object on the system is considered an SQL procedure and 
can be called using the SQL CALL statement without explicitly defining it to 
the database with a CREATE PROCEDURE.  (Though I recommend you always do the 
CREATE PROCEDURE).


Look at it this way

SQL Procedure == CL/RPG *PGM
SQL Function == RPGIV subprocedure.

An SQL procedure may make use of SQL functions.  But you probably don't want an 
SQL function calling a SQL procedure.

HTH,


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Mike 
> Pantzopoulos -
> (H/O)
> Sent: Wednesday, July 13, 2005 8:38 PM
> To: midrange-l@xxxxxxxxxxxx
> Subject: 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
> 
> **************************************************************
> *******************************************
> 
> -- 
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
>