Snowflake Statement not working within procedure

Question

I wrote an SQL Statement. When I execute the statement normally it works. But in a stored procedure I get the following error:

SQL compilation error: error line 1 at position 43 invalid identifier 'PA.S_ID' At Statement.execute, line 13 position 25

CREATE OR REPLACE PROCEDURE DWH.TEST.FILL_BUSHARES()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS 
$$

    function executeSQL(SQL ) {
               var rows_affected = 0;

          // Prepare statement.
          var stmt = snowflake.createStatement(
                 {
                 sqlText: SQL
                 }
              );
          // Execute Statement
          var res = stmt.execute();
          rows_affected = res.getNumRowsAffected();
          return rows_affected;
   
    }
    

    sql_fall1 = executeSQL("INSERT INTO DWH.TEMP.CM_BU_SHARES "+ 
                                " SELECT  pa.S_ID, " +
                                " pa.PRCTR, " +
                                " pa.MATNR, " +
                                " pa.KUNNR " +
                                " ,100 as invalid_Share     --00 " +
                                " ,0 as OFIS_Share          --10 " +
                                " ,0 as CREHO_Share         --20 " +
                                " ,0 as COWO_Share          --30 " +
                                " ,0 as TECH_Share          --40 " +
                                " ,0 as Lifestyle_Share     --50 " +
                                " ,0 as NISO_Share          --60 " +
                            "FROM DWH.TEMP.CMPREP_PROFITABILITYANALYSIS as PA " 
                    );
                                
    var returnStatement = "test" ;                        
    return returnStatement;    

    $$;

But the Statement is right. If I copy the Statement add it to Notepad++, crtl+h and replace all " with nothing and all + with nothing and I run it in the classic console it works.

"INSERT INTO DWH.TEMP.CM_BU_SHARES "+ 
                                " SELECT  pa.S_ID, " +
                                " pa.PRCTR, " +
                                " pa.MATNR, " +
                                " pa.KUNNR " +
                                " ,100 as invalid_Share     --00 " +
                                " ,0 as OFIS_Share          --10 " +
                                " ,0 as CREHO_Share         --20 " +
                                " ,0 as COWO_Share          --30 " +
                                " ,0 as TECH_Share          --40 " +
                                " ,0 as Lifestyle_Share     --50 " +
                                " ,0 as NISO_Share          --60 " +
                            "FROM DWH.TEMP.CMPREP_PROFITABILITYANALYSIS PA " 

I just can't explain myself why it won't work within a stored procedure. Google didn't help. The identifier just isn't wrong. It is not case sensitive. The same query works without the " and +.

Answer 1

I think your comment signs are breaking the SQL. Can you try this?

sql_fall1 = executeSQL("INSERT INTO DWH.TEMP.CM_BU_SHARES "+ 
                                " SELECT  pa.S_ID, " +
                                " pa.PRCTR, " +
                                " pa.MATNR, " +
                                " pa.KUNNR " +
                                " ,100 as invalid_Share     --00 \n" +
                                " ,0 as OFIS_Share          --10 \n" +
                                " ,0 as CREHO_Share         --20 \n" +
                                " ,0 as COWO_Share          --30 \n" +
                                " ,0 as TECH_Share          --40 \n" +
                                " ,0 as Lifestyle_Share     --50 \n" +
                                " ,0 as NISO_Share          --60 \n" +
                            "FROM DWH.TEMP.CMPREP_PROFITABILITYANALYSIS as PA " 
                    );

If it still fails, can you run without comments?

sql_fall1 = executeSQL("INSERT INTO DWH.TEMP.CM_BU_SHARES "+ 
                                " SELECT  pa.S_ID, " +
                                " pa.PRCTR, " +
                                " pa.MATNR, " +
                                " pa.KUNNR " +
                                " ,100 as invalid_Share      " +
                                " ,0 as OFIS_Share           " +
                                " ,0 as CREHO_Share          " +
                                " ,0 as COWO_Share           " +
                                " ,0 as TECH_Share           " +
                                " ,0 as Lifestyle_Share      " +
                                " ,0 as NISO_Share           " +
                            "FROM DWH.TEMP.CMPREP_PROFITABILITYANALYSIS as PA " 
                    );