Re: EXEC syntax for SP which returns row set

Mark A wrote:
> > "Mark Townsend" <markbtownsend_at_comcast.net> wrote in message
> > news:42D7429F.9040705_at_comcast.net...
> >>
> >> I would be interested in seeing one. I guessing in the syntax there has
> >> to be some sort of interface definition of the result set being returned,
> >> otherwise how does the calling program know what is going to come back at
> >> it ? Something somewhere has to allocate the memory, and either side of
> >> the interface, both program blocks have to agree how to refer to that
> >> memory.
> >
> Here is a DB2 SP that returns a result set, but has no other input or ouput
> parms:
>
> CREATE PROCEDURE db2inst1.test
> RESULT SETS 1
> LANGUAGE SQL
>
> BEGIN
> DECLARE C1 CURSOR WITH RETURN FOR
> SELECT * from department;
>
> OPEN C1;
>
> END
>
> Here is the command that can be run to execute the SP in DB2, and the
> output. Obviously, a program like java would have to allocate memory and
> process the rows returned one at a time. This is just suing a command
> processor (or OS command prompt) or like SQL*Plus.
>
> call db2inst1.test
>
> Here is the output from the above command:
>
> Result set 1
> --------------
>
> DEPTNO DEPTNAME MGRNO ADMRDEPT LOCATION
> ------ ----------------------------- ------ -------- ----------------
> A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00 -
> B01 PLANNING 000020 A00 -
> C01 INFORMATION CENTER 000030 A00 -
> D01 DEVELOPMENT CENTER - A00 -
> D11 MANUFACTURING SYSTEMS 000060 D01 -
> D21 ADMINISTRATION SYSTEMS 000070 D01 -
> E01 SUPPORT SERVICES 000050 A00 -
> E11 OPERATIONS 000090 E01 -
> E21 SOFTWARE SUPPORT 000100 E01 -
>
> 9 record(s) selected.
>
> Return Status = 0

There is no OUT parameter to thie procedure, so how can it compare to the stored procedure you coded for Oracle? If you're going to make comparisons do so on an 'apples to apples' basis. Show us a stored procedure, coded with an OUT parameter, which doesn't require that parameter to be passed.

I'm anzious to see your work.

David Fitzjarrell Received on Fri Jul 15 2005 - 07:52:19 CDT