Working with Stored Procedures and Result Sets

An OpenESQL SQL CLR or DBMS vendor-specific stored procedure can:

Accept input parameters
Return output parameters
Accept and return input/output parameters
Use positional or keyword parameters
Return one or more forward only read only result sets
Be called with parameter arrays

Note: The features provided by different database vendors vary considerably, and any given vendor might offer only a subset of the features listed above. For this reason, stored procedure calls are much less portable between data sources than other OpenESQL statements.

Calling a Stored Procedure that Returns No Result Sets

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

Calling a Stored Procedure that Returns One or More Result Sets

If a stored procedure returns one or more result sets, you must first declare it using the DECLARE CURSOR statement. For example:

         EXEC SQL 
                DECLARE cursorName CURSOR FOR storedProcedureCallStatement
         END-EXEC

You then call the stored procedure just as you would any other type of cursor using the OPEN statement to open the cursor, followed by the FETCH statement to fetch result set rows.

If your stored procedure is coded to return more than one result set, and your FETCH statement returns an SQLCODE of 100 (signifying the end of the current result set), you can issue the GET NEXT RESULT SET statement to fetch the next result set as follows:

          EXEC SQL
                 GET NEXT RESULT SET FOR cursorName
          END-EXEC

If your GET NEXT RESULT SET statement returns an SQLCODE of 0, another result set is available, and you can issue another GET NEXT RESULT SET to retrieve it. However, if GET NEXT RESULT SET returns an SQLCODE of 100, no additional result sets are available.

Using Array Parameters

ODBC parameters differ from Oracle array parameters. The effect of using a parameter array is the same as repeating the statement for each element of the array. On a stored procedure call, if one parameter is passed as an array, then all parameters must be arrays with the same number of elements. The stored procedure "sees" one call for each "row" of parameters. The number of rows passed can be limited to less than the full array size by preceding the call with the following phrase:

FOR :hvar

Where :hvar is an integer host variable containing a count of the number of rows to be passed.

Coding an OpenESQL SQL CLR Stored Procedure to Return Result Sets

You return multiple result sets to a calling program by declaring one or more cursors in a stored procedure, and defining each declared cursor to return multiple result sets by including multiple SELECT statements in its definition.

To return multiple result sets for a particular cursor, its DECLARE CURSOR statement in your stored procedure must open the cursor and also contain a SELECT statement for each result set you want to return. For example:

      EXEC SQL 
          DECLARE C1 CURSOR
          FOR
              SELECT CUSTID FROM COMPANY;
              SELECT CUSTNAME FROM COMPANY;
      END-EXEC
      EXEC SQL OPEN C1 END-EXEC

      EXEC SQL 
      DECLARE C2 CURSOR
      FOR
          SELECT COMPNAME FROM COMPANY;
      END-EXEC
      EXEC SQL OPEN C2 END-EXEC

In this example, we have two cursors, C1 and C2, with C1 returning two result sets, and C2 returning one result set. Therefore, these cursors return a total of three result sets to the calling application.

When you are done with cursor processing in the stored procedure itself, close each cursor using a CLOSE statement that includes the WITH RETURN clause. For example:

EXEC SQL CLOSE C1 WITH RETURN TO CALLER END-EXEC
EXEC SQL CLOSE C2 WITH RETURN TO CALLER END-EXEC

Once the cursors are closed, the result sets associated with them become available to the calling application.

Note: Any forward only read only cursor that is open when the stored procedure returns is also returned to the calling application.

How OpenESQL SQL CLR Stored Procedures and Result Sets Interact with OpenESQL Callers

SQL CLR stored procedures that return result sets interact differently with their caller than do other types of stored procedures. For example, with z/OS DB2, a called COBOL stored procedure executes all its code, and then returns control completely to the caller. This is not the case with SQL CLR COBOL stored procedures. Control is returned to the caller after an EXEC SQL CLOSE cursor with RETURN TO CALLER is executed. After the caller consumes the corresponding result set (that is, it FETCHes until SQLCODE=100), control is then returned back to the SQL CLR stored procedure, which can then execute additional code. Therefore, in the case of a SQL CLR stored procedure, the stored procedure code itself is essentially placed in a wait state after executing the CLOSE cursor statement.

To get around this, we suggest that if possible, place all CLOSE with RETURN statements at the end of your stored procedure code rather than dispersing them throughout the stored procedure. This minimizes the number of interactions created by a SQL CLR stored procedure call, and basically eliminates the side effects that a high volume of interactions can create.

Keep in mind that with SQL CLR stored procedures, all result sets must be consumed first by the caller before output parameter values are available.