Question Re: Execute immediate returning a table as output in Technology Q&A

Execute immediate returning a table as output

mocasio — Sat, 11 Jan 2025 16:36:53 GMT

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)

Re: Execute immediate returning a table as output

Sandra_Rossi — Sat, 11 Jan 2025 18:35:43 GMT

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.

Re: Execute immediate returning a table as output

mjocasio — Sat, 11 Jan 2025 18:51:39 GMT

Is a syntax error not a execution error

Re: Execute immediate returning a table as output

Sandra_Rossi — Sat, 11 Jan 2025 19:35:29 GMT

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.

Re: Execute immediate returning a table as output

Sandra_Rossi — Sun, 12 Jan 2025 11:05:50 GMT

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.

Re: Execute immediate returning a table as output

Sandra_Rossi — Sun, 12 Jan 2025 16:47:30 GMT

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":