467,209 Members | 1,319 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,209 developers. It's quick & easy.

Stored Procedures / UDF

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
  • viewed: 1689
Share:
3 Replies
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
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
Thank you, that helped a lot, I realized it with a UDF similar to
Brian's.

Mar 24 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Kent Lewandowski | last post: by
5 posts views Thread by Tim Marshall | last post: by
45 posts views Thread by John | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.