To implement most data retrieval options associated with a stored procedure, (excluding those that involve loading retrieved data into temp-tables) you must define the RUN STORED-PROC statement and explicitly end the stored procedure using the CLOSE STORED-PROC statement.

You must define the RUN STORED-PROC and explicitly complete access to the stored procedure OUTPUT with the CLOSE STORED-PROC syntax expressions for a stored procedure implementation that retrieves the following types of data from the foreign data source:

  • proc-text-buffer
  • user-defined views

For more information about these data retrieval options, see Data output and retrieval options.

Note: You can also implicitly or explicitly close a stored procedure to retrieve these types of data: return code or output parameters. For more information about each of these data retrieval options, see Data output and retrieval options.

The syntax details presented in this section describe how to run and close a stored procedure in the MS SQL Server data source.

This is the syntax for the RUN STORED-PROC statement to execute an RDBMS stored procedure without the LOAD-RESULT-INTO phrase:

RUN STORED-PROC procedure  [ int = PROC-HANDLE ]
              [ NO-ERROR ]
   [ ( [ INPUT | OUTPUT | INPUT OUTPUT ]
                  [ PARAM parameter-name = ] expression, ...
   [ INPUT | OUTPUT | INPUT OUTPUT ]
                  [ PARAM parameter-name = ] expression ) ]

This type of syntax expression requires the explicit CLOSE STORED-PROC statement. This is the partial syntax for the CLOSE STORED-PROC statement:

CLOSE STORED-PROC procedure
    [ integer-field = PROC-STATUS ]
       [ WHERE PROC-HANDLE = integer-field ]