Working With HCOSS SQL CLR Stored Procedures and Result Sets

An HCOSS SQL CLR stored procedure can:

Accept input parameters
Return output parameters
Accept and return input/output parameters
Return one or more 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.

Coding a SQL CLR Stored Procedure to Return Result Sets

If you want your calling program to receive a result set from your SQL CLR stored procedure, the stored procedure must include a DECLARE CURSOR statement that specifies an allocated cursor in the calling program, and also specifies the WITH RETURN clause. The stored procedure must also open the cursor. For example:

   EXEC SQL
       DECLARE C1 SCROLL CURSOR 
            WITH RETURN TO CALLER 
            FOR 
               SELECT CUSTOMERID from CUSTOMERS
   END-EXEC
   EXEC SQL
         OPEN C1
   END-EXEC

Returning Stored Procedure Result Sets

COBOL stored procedures compiled with SQL(DIALECT=MAINFRAME) emulate mainframe behavior and return result set locators rather than actual result sets. When calling applications written in languages other than COBOL and PL/I, and for COBOL and PL/I applications that have not been compiled with SQL(DIALECT=MAINFRAME), build your stored procedures with SQL(OPTION=NORSLOCATORS). Result sets are then returned as regular SQL Server result sets.