SnowPro Core Certification — Question 331

How would a user execute a series of SQL statements using a task?

Answer options

• A. Include the SQL statements in the body of the task CREATE TASK mytask .. AS INSERT INTO target1 SELECT .. FROM stream_s1 WHERE .. INSERT INTO target2 SELECT .. FROM stream_s1 WHERE ..
• B. A stored procedure can have only one DML statement per stored procedure invocation and therefore the user should sequence stored procedure calls in the task definition CREATE TASK mytask .... AS call stored_proc1(); call stored_proc2();
• C. Use a stored procedure executing multiple SQL statements and invoke the stored procedure from the task. CREATE TASK mytask .... AS call stored_proc_multiple_statements_inside();
• D. Create a task for each SQL statement (e.g. resulting in task1, task2, etc.) and string the series of SQL statements by having a control task calling task1, task2, etc. sequentially.

Correct answer: C

Explanation

The correct answer is C because it allows for the execution of multiple SQL statements within a single stored procedure, which can be called from the task. Option A is incorrect as it suggests including multiple SQL statements directly in the task, which is not the intended use. Option B is wrong because a stored procedure can contain multiple DML statements, and it misrepresents the capabilities of stored procedures. Option D is inefficient since it requires creating multiple tasks for each SQL statement, which is not necessary.