Combining stored procedure and a select statement - possible?

[Terentius Neo]

Is it possible to combine (in DB2 UDB 8.1) a stored procedure and a
select statement? I mean something like this:

Select
c.number,
call procedure( c.number ) as list
from
table c

With best regards
TN

[Larry Menard]

Perhaps you are thinking of a User-Defined Function (UDF)?

--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"
"Terentius Neo" <te***********@here.fi> wrote in message
news:B2**************@read3.inet.fi...

Is it possible to combine (in DB2 UDB 8.1) a stored procedure and a select
statement? I mean something like this:

Select
c.number,
call procedure( c.number ) as list
from
table c

With best regards
TN

[Serge Rielau]

Terentius Neo wrote:

Is it possible to combine (in DB2 UDB 8.1) a stored procedure and a
select statement? I mean something like this:

Select
c.number,
call procedure( c.number ) as list
from
table c

No. The SQL Standard has a clear distinction between procedures and
functions. What you need is a table function.

Procedures can have INOUT or OUT parameters and also return multiple (or
no) result sets. Neither can be fitted into the model (although some
vendors are mating what IMHO mustn't be mated).

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Terentius Neo]

Larry Menard wrote:

Perhaps you are thinking of a User-Defined Function (UDF)?

Yes, perhaps :-) If a UDF can loop through an other table and return one
string compiled from the information in the table. Can it be done?

TN

[Serge Rielau]

Terentius Neo wrote:

Larry Menard wrote:

Perhaps you are thinking of a User-Defined Function (UDF)?

Yes, perhaps :-) If a UDF can loop through an other table and return one
string compiled from the information in the table. Can it be done?

That smells more like a scalar function to me, but hey, your wish is my
command:

db2 -td@
CREATE FUNCTION CONCAT_AGG(a INT) RETURNS TABLE(str VARCHAR(1000))
BEGIN ATOMIC
DECLARE str VARCHAR(1000) DEFAULT '';
FOR row AS SELECT c1 FROM T WHERE c2 = a DO
SET str = c1 || ',' || str;
END FOR;
RETURN VALUES(SUBSTR(str, 1, LENGTH(str - 1));
END
@

A user defined aggregate function (untested), sort of...

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Terentius Neo]

Serge Rielau wrote:

That smells more like a scalar function to me, but hey, your wish is my
command: <snip> A user defined aggregate function (untested), sort of...

Excellent! Thanks for the help.

Terentius

[amurchis]

I agree with Serge. It looks like you either want a table function or a
stored procedure that returns a result set.

You'd invoke a table function like:

SELECT <columns> FROM TABLE(UDF(number)) AS TableUDF

<columns>
================
<result1>
<result2>
....
Table UDFs are hard to describe here, but essentially they are passed
the input data, and depending on the type of call (also passed in) will
either open the data for return, process the data for a fetch, or clean
up after everything is done.

Alternately, if you simply want to call a procedure and pass it a value,
and have it run a query against a table for you based on that value,
stored procedures have the ability to return result sets to the
immediate caller of the procedure (or, for C & SQL procedures, to the
client application).

For C/SQL, this is something like:

DECLARE CURSOR C1 WITH RETURN TO [CALLER | CLIENT] FOR <etc>;

Each language has different rules, and depending on the language may
even have different rules for the parameter style. For instance,
LANGUAGE C uses the same method for each parameter style supported, but
for LANGUAGE JAVA, PARAMETER STYLE DB2GENERAL and PARAMETER STYLE JAVA
handle returning resultsets quite differently. Check the documentation
for more details.

Note that you'll need to check to see how your client supports handling
result sets returned from procedures, as it varies by client. For
instance, I don't think embedded C applications can handle them
(somewhat odd considering an embedded C procedure can OPEN a resultset
for returning), but anything that's CLI (JAVA/.NET/CLP) based will do so
with no problems.

I'm not quite sure which scenario fits your needs. Typically, you'd use
a stored procedure that returns one (or more) result sets if you simply
want to centralize a few queries rather than distributing them among
multiple applications. A table function, on the other hand, is useful
if you want to convert non-DB2 data into a query, such as passing in a
name of a formatted data file as input into the table UDF, opening that
file, and fetching results from it.

I hope that helps!

Serge Rielau wrote:

Terentius Neo wrote:

Is it possible to combine (in DB2 UDB 8.1) a stored procedure and a
select statement? I mean something like this:

Select
c.number,
call procedure( c.number ) as list
from
table c

No. The SQL Standard has a clear distinction between procedures and
functions. What you need is a table function.

Procedures can have INOUT or OUT parameters and also return multiple (or
no) result sets. Neither can be fitted into the model (although some
vendors are mating what IMHO mustn't be mated).

Cheers
Serge