What is SQL Exec

SAP NetWeaver AS ABAP Release 750, © Copyright 2016 SAP AG. All rights reserved.

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

EXEC SQL - EXECUTE

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 parameter transfer can be very different for different database systems, there is a standardized command for statically embedded Native SQL.

The statement EXECUTE PROCEDURE calls a procedure proc stored in the database system. Actual parameters separated by commas must be specified for all formal parameters of the procedure. Before each actual parameter, IN, OUT or INOUT must be used to specify whether it is an input, output or input / output parameter. Literals or host variables marked with a colon (:) can be used for the actual parameters, which in this case can also be internal tables. If the tables are internal, they must be standard tables without secondary table keys.

Note

The stored procedures of the SAP HANA database are database procedures written in SQLScript. The ABAP Managed Database Procedures (AMDP) as well as the special statement CALL DATABASE PROCEDURE, which also allows access via a secondary database connection, are available in ABAP for managing and calling them.

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 increases each flight price for the client "000" in the table SFLIGHT by a certain 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

Definition of a procedure selfunc using database-specific SQL statements (Informix), calling the procedure with the SAP-specific Native SQL statement EXECUTE PROCEDURE in a LOOP using a selection table and deleting the procedure using an SQL statement. In the case shown here, the procedure is a function whose return value output is transferred to the host variable name in EXECUTE PROCEDURE.

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 ().