ABAP Keyword Documentation → ABAP − Reference → Processing External Data → ABAP Database Access → Native SQL → ADBC - ABAP Database Connectivity → ADBC - CL_SQL_STATEMENT

ADBC - Stored Procedures

Stored procedures can be executed using the following instance method of the class CL_SQL_STATEMENT

EXECUTE_PROCEDURE

The method has an mandatory input parameter PROC_NAME of type string, which must be passed the name of an existing stored procedure. In the same way as in DML statements, it is possible to bind ABAP data objects as actual parameters to the formal parameters of the stored procedure using the method SET_PARAM. The type of parameter must be specified using the additional parameter INOUT. Possible values are defined in the constants C_PARAM_IN, C_PARAM_OUT, and C_PARAM_INOUT of the class CL_SQL_STATEMENT. C_PARAM_IN is the default value. The order of the calls determines the assignment to the formal parameters from left to right.

Other versions: 7.31 | 7.40 | 7.54

Notes

The methods SET_PARAM_STRUCT and SET_PARAM_TABLE cannot be used. This means that structures and internal tables cannot be bound to actual parameters. On databases where this is allowed, a stored procedure can also be called using the method EXECUTE_QUERY. This method makes it possible to bind internal tables to the results set. See the example below.

On the SAP HANA database, the stored procedures are database procedures written in SQLScript. In ABAP, the ABAP Managed Database Procedures (AMDP) are available for managing and calling these stored procedures. The special statement CALL DATABASE PROCEDURE makes it possible to access them using a secondary connection.

EXECUTE_PROCEDURE can be used to call procedures with input and output parameters but cannot be used to call functions with return values. In SQL, functions are used directly in suitable operand positions.

When parameters are submitted, mapping takes place between the ABAP types and the database types. The ABAP types must match the database types. If they do not match, conversions must be made in the Native SQL interface. These conversions are platform-dependent and can raise exceptions.

Example

Uses ADBC to call an SQLScript procedure in the SAP HANA database managed using AMDP. Before the ADBC call, a method of the class CL_AMDP_RUNTIME_SERVICES must be applied to ensure that the procedure exists on the database. This would not be necessary if the associated AMDP method were used for the call.

IF cl_db_sys=>is_in_memory_db = abap_false. 
  RETURN. 
ENDIF. 
TRY. 
    cl_amdp_runtime_services=>prepare_method_for_call( 
      `CL_DEMO_AMDP=>INCREASE_PRICE` ). 
  CATCH cx_amdp_runtime_services. 
    RETURN. 
ENDTRY. 

DATA incprice TYPE sflight-price VALUE '0.5'. 
TRY. 
    DATA(sql) = NEW cl_sql_statement( ). 
    sql->set_param( data_ref = REF #( sy-mandt ) 
                    inout    = cl_sql_statement=>c_param_in ). 
    sql->set_param( data_ref = REF #( incprice ) 
                   inout    = cl_sql_statement=>c_param_in ). 
    sql->execute_procedure( 
      proc_name = '"CL_DEMO_AMDP=>INCREASE_PRICE"' ). 
    cl_demo_output=>display( `Price increased successfully` ). 
  CATCH cx_sql_exception INTO DATA(exc). 
    cl_demo_output=>display( exc->get_text( ) ). 
ENDTRY.

Example

Calls a stored procedure of the SAP HANA database using the method EXECUTE_QUERY. The stored procedure is an AMDP-managed SQLScript procedure with a tabular output parameter. The null value is bound to the output parameter to create a temporary table for this parameter on the database. This table then can be joined to an internal table result using SET_PARAM_TABLE.

IF cl_db_sys=>is_in_memory_db = abap_false. 
  RETURN. 
ENDIF. 
TRY. 
    cl_amdp_runtime_services=>prepare_method_for_call( 
      `CL_DEMO_AMDP_SCARR=>SELECT_SCARR` ). 
  CATCH cx_amdp_runtime_services. 
    RETURN. 
ENDTRY. 

DATA result TYPE cl_demo_amdp_scarr=>scarr_tab. 
TRY. 
    DATA(query_result) = NEW cl_sql_statement( 
       )->execute_query( statement = 
        `CALL "CL_DEMO_AMDP_SCARR=>SELECT_SCARR"( '` && 
         sy-mandt && `', NULL )` ). 
    query_result->set_param_table( itab_ref = REF #( result ) ). 
    query_result->next_package( ). 
    cl_demo_output=>display( result ). 
  CATCH cx_sql_exception INTO DATA(exc). 
    cl_demo_output=>display( |Exception\n{ exc->get_text( ) 
                                     }\n{ exc->sql_message }| ). 
    RETURN. 
ENDTRY.

Executable Example

ADBC, Stored Procedure