Retrieving multiple result sets in a stored procedure

[Michael_Fischer]

Hi,

this is not a question. It's a solution. Maybe it's an doc enhancement request. 🙂

We need to process multiple result sets (coming from a CLR external procedure. It took me half an hour to get this running and I just want to share the solution with you. The key is the RESUME statement. I think there should be a usage example of it in the "SQL Anywhere Server - SQL Usage » Stored procedures, triggers, batches, and user-defined functions » Result sets" section of the documentation.

Here's the sample:

create or replace procedure ResultSetTest()
begin
    select  'OK'            ProcResult
            , 'No messages' ProcMessage;

    select  Id, Surname, GivenName
    from    Customers
    order   by Id;
end;

create or replace procedure ResultSetTestConsumer()
begin
    declare customers cursor for 
       call ResultSetTest();

    declare @ID         integer;
    declare @Surname    person_name_t;
    declare @GivenName  person_name_t;

    declare @ProcResult     char(10);
    declare @ProcMessage    long varchar;

    open customers;

    fetch next customers into @ProcResult, @ProcMessage;
    message String('[', @ProcResult, '] ', @ProcMessage) type info to client;

    if @ProcResult = 'OK' then
        resume customers;

        lp: Loop
            fetch next customers into @ID, @Surname, @GivenName;
            if SQLCODE <> 0 then leave lp end if;
            message String('(', @ID, ') ', @Surname, ', ', @GivenName) type info to client;
        end loop;
    end if;

    close customers;
end;

call ResultSetTestConsumer();

[VolkerBarth]

A really helpful suggestion - and a DCX comment might be worthwhile...

FWIW: For old-school users of dbisqlc, the RESUME statement is not that unfamiliar - it's the only way to receive more than one result set of a procedure: 🙂

[Michael_Fischer]

Good point, Volker, just added a comment on DCX for SQLA17 under the "Result Sets" topic.

[Former Member]

Some of this is covered in the DCX article Store procedures with result sets at the bottom; almost as an after-thought/footnote. But I agree it would be awkward/difficult to map the information from this (Embedded-SQL-centric) article to a PSM usage ... for instance you were able to bypassed the need to describe the new result set (something that is not a PSM concept anyway) by knowing what this new result columns look like and directly representing that in your Fetch-Into statement.

There are numerous other DCX articles that cover this for client apps (dbisql not dbisqlc) and APIs ODBC, JDBC, Ruby, etc ... so maybe it makes sense for this to get it's own article. It does seem to be something that probably was not as much of a requirement since procedures usually are the 'return-or-of' multiple sets and less commonly a 'return-ee-for' multiple result sets and don't often have a needs to retrieve them.

Some possible caveats: this is a Watcom SQL behaviour (and not only becuase the RESUME statement is proprietary), ... and T/SQL stored procedures will likely behave differently. That latter caveat not being a concern for this case but may be important for other readers of this thread to be aware of.

[Michael_Fischer]

Nick,

it is indeed the first time since starting with Watcom SQL that I need to do this. In a plain SQL world we would find better designs. In our given real-world situation, the ResultSetTest()-Procedure is a proxy proc to an external CLR call that shall return both elaborate diagnostic information if something went wrong (first result set) and the actual data (second result set) if everything is ok.

I'm open for sugestions. Do you see a more elegant way to design that external interface?

[VolkerBarth]

Does it need to be one single procedure call?

Or could you split up the functionality into, say

• a procedure that returns the actual data as result set (empty in case of a failure) and an addional return code that tells about success/error, and
• an additional procedure that returns the diagnostic data if a failure has occured?

(Apparently that split assumes that failures are reproducible or that their diagnostic details are stored internally so the second procedure has that information available...)

Note that SQL Anywhere requires the caller of a procedure with both a result set and output parameters/return value to fully fetch all result sets before the parameters/return value are available, as stated by Mark here. (For that reason, I usually do only use one of both ways to return data...)

[Former Member]

Another possible thought ....

Maybe your 'elaborate diagnostic information' would fit better into some sort of structured object ... say HTML or XML maybe? The would then only require a static blob/clob to be fetched.

Both the .Net framework and possibly your ultimate content serving source may be able to provide that.

But if you are consuming the result from a pre-exisiting or third party component that does not support a different paradigm then you will be locked into the current approach.