Execute immediate returning a table as output

[mocasio]

the following code does not compile

CREATE OR REPLACE PROCEDURE schema.GET_MISSING_ROWS(
IN source_table NVARCHAR(50),
IN target_table NVARCHAR(50),
IN select_clause NVARCHAR(50),
IN where_clause NVARCHAR(50),
OUT result_rows TABLE (EMPLID VARCHAR(11), EMPL_RCD INTEGER, EFFDT DATE, EFFSEQ INTEGER, GVT_SF50_REMARK VARCHAR(3))
)

LANGUAGE SQLSCRIPT
AS
BEGIN
-- Construct and execute dynamic SQL to find missing rows
DECLARE sql_stmt NVARCHAR(5000);

sql_stmt := 'SELECT ' + :select_clause + ' FROM ' + :source_table + ' src ' + 'WHERE NOT EXISTS (SELECT 1 FROM ' || :target_table || ' tgt WHERE ' || :where_clause || ')';
result_rows := EXECUTE IMMEDIATE (:sql_stmt);
END;

error:

Could not execute 'CREATE OR REPLACE PROCEDURE schema.GET_MISSING_ROWS( IN source_table NVARCHAR(50), IN ...'
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "IMMEDIATE": line 16 col 28 (at pos 672)

[Sandra_Rossi]

You should try to locate the error cause. Try EXECUTE IMMEDIATE with hardcoded SELECT. Describe everything you have tried, and the result summary for each try.

[mjocasio]

Is a syntax error not a execution error

[Sandra_Rossi]

My answer was for a syntax error: You should try to locate the error cause. Try EXECUTE IMMEDIATE with hardcoded SELECT. Describe everything you have tried, and the result summary for each try.

[Sandra_Rossi]

No syntax error for me from ABAP code with little changes on variable names:

EXEC SQL.
CREATE OR REPLACE PROCEDURE SAPHANADB.ZZSRO_TEST(
IN source_table NVARCHAR(50),
IN target_table NVARCHAR(50),
IN select_clause NVARCHAR(50),
IN where_clause NVARCHAR(50),
OUT result_rows TABLE (EMPLID VARCHAR(11), EMPL_RCD INTEGER, EFFDT DATE, EFFSEQ INTEGER, GVT_SF50_REMARK VARCHAR(3))
)

LANGUAGE SQLSCRIPT
AS
BEGIN
-- Construct and execute dynamic SQL to find missing rows
DECLARE sql_stmt NVARCHAR(5000);

sql_stmt := 'SELECT ' + select_clause + ' FROM ' + source_table + ' src ' + 'WHERE NOT EXISTS (SELECT 1 FROM ' || target_table || ' tgt WHERE ' || where_clause || ')';
result_rows := EXECUTE IMMEDIATE sql_stmt;
END;
ENDEXEC.

[Sandra_Rossi]

I get the same syntax error if I embed your code in an AMDP procedure, but this message is not the root cause. The root cause is shown in red color here, concerning CREATE:

You can easily see the right cause by commenting the line "EXECUTE IMMEDIATE":

SQLSCRIPT message: feature not supported: DDL statements other than CREATE/DROP TABLE is/are not supported in procedure

---

Consider that the AMDP procedure corresponds itself to a database procedure created in HANA via CREATE PROCEDURE, so you have to check the documentation of CREATE PROCEDURE (https://help.sap.com), to see the permitted statements in CREATE PROCEDURE and so in an AMDP procedure.

You can see below that CREATE [OR REPLACE] PROCEDURE is not part of the permitted statements:

PS: for information, here's the minimal ABAP-AMDP-SQLSCRIPT reproducible example to get the error "sql syntax error: incorrect syntax near "IMMEDIATE"":

CLASS zzsro_amdp DEFINITION
  PUBLIC FINAL
  CREATE PUBLIC.
  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.
    METHODS test RAISING cx_amdp_error.
ENDCLASS.
CLASS zzsro_amdp IMPLEMENTATION.
  METHOD test BY DATABASE PROCEDURE
      FOR HDB 
      LANGUAGE SQLSCRIPT.
    CREATE OR REPLACE PROCEDURE schema.dbproc() LANGUAGE SQLSCRIPT AS
    BEGIN
    dummy := EXECUTE IMMEDIATE '';
    END;
  ENDMETHOD.
ENDCLASS.

PS 2: minimal ABAP-AMDP-SQLSCRIPT reproducible example showing that CREATE [OR REPLACE] PROCEDURE is not permitted (error "SQLSCRIPT message: feature not supported: DDL statements other than CREATE/DROP TABLE is/are not supported in procedure":

CLASS zzsro_amdp DEFINITION
PUBLIC FINAL
CREATE PUBLIC.
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb.
METHODS test RAISING cx_amdp_error.
ENDCLASS.
CLASS zzsro_amdp IMPLEMENTATION.
METHOD test BY DATABASE PROCEDURE
FOR HDB
LANGUAGE SQLSCRIPT.
CREATE OR REPLACE PROCEDURE schema.dbproc() LANGUAGE SQLSCRIPT AS
BEGIN
END;
ENDMETHOD.
ENDCLASS.