SnowPro Advanced: Data Engineer — Question 70

The following stored procedure has been created to load orders for a single date into a target table:

create or replace procedure uspLoadOrdersByDate ()
returns string
language javascript
execute as owner
as
'
sqlCmd = `INSERT INTO ORDERS_BY_DATE_TGT SELECT * FROM ORDERS_BY_DATE WHERE O_ORDERDATE = $dateToProcess`; sqlStmt = snowflake.createStatement({sqlText: sqlCmd}); res = sqlStmt.execute(); res.next(); return res.GetColumnValue(1);
';

Upon executing the following statements, the following error is returned:

set dateToProcess = '2020-08-02';
call uspLoadOrdersByDate();

Execution error in store procedure USPLOADORDERSBYDATE: Use of session variable '$DATETOPROCESS' is not allowed in owners rights stored procedure At Statement.execute, line 4 position 14

Making what changes will allow the procedure to execute successfully? (Choose two.)

Answer options

• A. Change the procedure to EXECUTE AS CALLER.
• B. Create a scheduled task to execute the procedure each day, and set dateToProcess = CURRENT_DATE() as a session parameter at the task level.
• C. Modify the body of the procedure to cast $dateToProcess to DATE within the WHERE clause of the INSERT statement.
• D. Change the procedure to accept $dateToProcess as an input parameter so that no session variables are used within the procedure itself.
• E. Rename the session variable to upper case to allow the procedure to read from it.

Correct answer: A, D

Explanation

Option A is correct because changing to EXECUTE AS CALLER allows the procedure to access session variables without permission issues. Option D is also correct as it avoids using session variables entirely by passing the date as an input parameter. The other options either do not resolve the permission issue or introduce unnecessary complexity.