Stored Procedures / UDF

Michael Rechberger

Hey

I am studying DB2 right know and trying to make some things to work. I
noticed that I might understood some things wrong. Could you help me
clarify a bit?

That's what I wanted to do: A function/procedure I can call in a SQL.
This function queries the database and returns a value. I thought I can
write a stored proc like...

CREATE PROCEDURE DB2ADMIN.getLoadId ( IN p_table_name CHAR(30)
, IN p_load_grp_id INTEGER
, OUT p_load_id INTEGER)
LANGUAGE SQL
SPECIFIC getLoadId
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
getId: BEGIN
SELECT load_info.load_id INTO p_load_id
FROM
load_info LEFT JOIN loads ON
load_info.load_id = loads.load_id
WHERE
load_grp_id = p_load_grp_id AND
load_table = p_table_name;
END getId

....and use this in a sql query like...

******
SELECT * FROM employee WHERE employee.load_info =
getLoadId('employee',1,?)
******

But obviously you cannot call a stored procedure in a SQL query?

Then I wanted to do the same stuff with a UDF but I read somewhere that
an external scalar function cannot query the database??
Now I am confused... Is there a way to do what I want?

Thank you...
Michael

Mar 17 '06 #1

Brian Tkatch

PROCEDURE take procedural action on the database, and as such cannot be
CALLed from within a SQL query. They must be CALLed with the CALL
keyword.

A UDF can be used in the query. I am not sure of the limitation on
which queries it can execute. I would try using one, as in:

CREATE FUNCTION getLoadId(p_table_name CHAR(30), p_load_grp_id INTEGER)
RETURNS INTEGER
RETURN SELECT load_info.load_id FROM
load_info LEFT JOIN loads ON
load_info.load_id = loads.load_id
WHERE
load_grp_id = p_load_grp_id AND
load_table = p_table_name

B.

Mar 17 '06 #2

Knut Stolze

Michael Rechberger wrote:

Hey

I am studying DB2 right know and trying to make some things to work. I
noticed that I might understood some things wrong. Could you help me
clarify a bit?

That's what I wanted to do: A function/procedure I can call in a SQL.
This function queries the database and returns a value. I thought I can
write a stored proc like...

CREATE PROCEDURE DB2ADMIN.getLoadId ( IN p_table_name CHAR(30)
, IN p_load_grp_id INTEGER
, OUT p_load_id INTEGER)
LANGUAGE SQL
SPECIFIC getLoadId
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
getId: BEGIN
SELECT load_info.load_id INTO p_load_id
FROM
load_info LEFT JOIN loads ON
load_info.load_id = loads.load_id
WHERE
load_grp_id = p_load_grp_id AND
load_table = p_table_name;
END getId

...and use this in a sql query like...

******
SELECT * FROM employee WHERE employee.load_info =
getLoadId('employee',1,?)
******

But obviously you cannot call a stored procedure in a SQL query?

Then I wanted to do the same stuff with a UDF but I read somewhere that
an external scalar function cannot query the database??

You can query the database. You can use a SQL-bodied function, i.e. one
declared with LANGUAGE SQL, or you use an external routine written in C/C++
or Java. Either way, queries can be used w/o any problem. Brian already
gave you a solution for a LANGUAGE SQL UDF.

If possible, I'd recommend to stick with SQL-bodied functions. They are
compiled into the SQL statement that invokes the function and, thus, gives
the optimizer a much better chance to optimize the overall query. This is
not possible with external code that can influence which (sub-)statements
are executed.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Mar 20 '06 #3

Michael Rechberger

Thank you, that helped a lot, I realized it with a UDF similar to
Brian's.

Mar 24 '06 #4