ABAP Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP Database Accesses →  Native SQL →  EXEC SQL - Embedded Native SQL →  EXEC SQL 

EXEC SQL - EXECUTE

Other versions: 7.31 | 7.40 | 7.54

Syntax

EXEC SQL. 
  EXECUTE PROCEDURE proc ( IN    p_in1    IN    p_in2 ..., 

              
            OUT   p_out1   OUT   p_out2 ..., 

              
            INOUT p_inout1 INOUT p_inout2 ... ) 
ENDEXEC. 

Effect

In database systems, procedures can be defined as stored procedures. Since the syntax for calling such procedures and the associated pass by parameter can vary widely for various database systems, a uniform command exists in statically embedded Native SQL.

The statement EXECUTE PROCEDURE calls a procedure proc stored in the database. For all formal parameters of the procedure, the actual parameters must be specified, separated by commas. IN, OUT, or INOUT must be specified before every actual parameter, to indicate whether the parameter is an input, output, or input/output parameter. For the actual parameters, literals or host variables denoted by a colon (:) can be used, which can also be internal tables in this case. If they are internal tables, these must be standard tables without secondary table keys.

Note

On the SAP HANA database, the stored procedures are database procedures written in SQLScript. In ABAP, these procedures can be managed and called using ABAP Managed Database Procedures (AMDP) and the special statement CALL DATABASE PROCEDURE. This statement enables access using a secondary database connection.

Example

Definition of a procedure incprice using database-specific SQL statements (Oracle) and calling the procedure with the SAP-specific Native SQL statement EXECUTE PROCEDURE. The execution of the program section raises the price of every flight for the client "000" in the table SFLIGHT by a specific amount.

PARAMETERS incprice TYPE sflight-price. 

EXEC SQL. 
  CREATE OR REPLACE PROCEDURE increase_price (x IN NUMBER) IS 
  BEGIN 
    UPDATE sflight SET price = price + x 
           WHERE mandt = '000'; 
  END; 
ENDEXEC. 

EXEC SQL. 
  EXECUTE PROCEDURE increase_price ( IN :incprice ) 
ENDEXEC. 

Example

This example defines a selfunc procedure using database-specific SQL statements (Informix). It also calls the procedure using the SAP-specific Native SQL statement EXECUTE PROCEDURE in a LOOP loop by means of a selection table, and deletes the procedure using an SQL statement. In the case shown here, the procedure is a function whose return value output in EXECUTE PROCEDURE is passed to the host variable name.

DATA scarr_carrid TYPE scarr-carrid. 
SELECT-OPTIONS s_carrid FOR scarr_carrid NO INTERVALS. 
DATA s_carrid_wa LIKE LINE OF s_carrid. 

DATA name TYPE c LENGTH 20. 

TRY. 
    EXEC SQL. 
      CREATE FUNCTION selfunc( input CHAR(3) ) 
        RETURNING char(20); 
        DEFINE output char(20); 
        SELECT carrname 
               INTO output 
               FROM scarr 
               WHERE mandt  = '000' AND 
                     carrid = input; 
        RETURN output; 
        END FUNCTION; 
    ENDEXEC. 
    LOOP AT s_carrid INTO s_carrid_wa 
                    WHERE sign = 'I' AND option = 'EQ'. 
      TRY. 
         EXEC SQL. 
            EXECUTE PROCEDURE selfunc( IN  :s_carrid_wa-low, 
                                      OUT :name ) 
          ENDEXEC. 
          cl_demo_output=>write( |{ s_carrid_wa-low } { 
                                           name }| ). 
        CATCH cx_sy_native_sql_error. 
         cl_demo_output=>write_text( `Error in procedure execution` ). 
      ENDTRY. 
    ENDLOOP. 
    EXEC SQL. 
      DROP FUNCTION selfunc; 
    ENDEXEC. 
  CATCH cx_sy_native_sql_error. 
    cl_demo_output=>write_text( `Error in procedure handling` ). 
ENDTRY. 
cl_demo_output=>display( ).