Working With HCOSS SQL CLR Stored Procedures and Result Sets

Calling a SQL CLR Stored Procedure that Returns No Result Sets

To call an HCOSS stored procedure that returns no result sets, use the CALL embedded SQL statement. For complete details on using the CALL statement, see the topic CALL.

Receiving Result Sets From a SQL CLR Stored Procedure

If a stored procedure returns one or more result sets, you receive the result sets using a series of embedded SQL statements. First, use the CALL statement to call the stored procedure. Next, use the ASSOCIATE LOCATORS statement to define an appropriate number of result sets. Then, issue one ALLOCATE CURSOR statement for defined result set. For example, the following code returns two result sets by associating two result set locators with two allocated cursors, in effect mapping the result sets to the cursors:

     EXEC SQL CALL MYPROC(:hv-country) 
     END-EXEC
     EXEC SQL
        ASSOCIATE LOCATORS (:RS1,:RS2) WITH PROCEDURE MYPROC
     END-EXEC 
     EXEC SQL
         ALLOCATE C1 CURSOR FOR RESULT SET :RS1
     END-EXEC
     EXEC SQL
         ALLOCATE C2 CURSOR FOR RESULT SET :RS2
     END-EXEC

At this point, use the FETCH statement to fetch each result set, just like any other SQL cursor. You can also interleave access to these cursors at the same time. For example, using the scenario provided above, you could fetch from C1, and then immediately fetch from C2.

Finally, when you are done with cursor processing, use the CLOSE statement to end result set processing.