Re: EXEC syntax for SP which returns row set

> "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 Received on Fri Jul 15 2005 - 00:21:57 CDT