A database contains a table and a stored procedure defined as: The log_table is initially empty and a Data Engineer issues the following command: CALL insert_log(NULL::VARCHAR); No other operations…

Question

Exhibit

Options

• AThe log_table contains zero records and the stored procedure returned 1 as a return value.
• BThe log_table contains one record and the stored procedure returned 1 as a return value.
• CThe log_table contains one record and the stored procedure returned NULL as a return value.
• DThe log_table contains zero records and the stored procedure returned NULL as a return value.

• A
  10% (2)
• B
  5% (1)
• D
  86% (18)

Explanation

Option D is correct because SQL's NULL propagation rules govern both outcomes: when NULL is passed as the argument, any conditional logic inside the procedure (such as a WHERE clause or IF check comparing the parameter) evaluates to NULL or FALSE rather than TRUE, so the INSERT never executes and log_table remains empty, while the return value - derived from an expression involving the NULL input - also evaluates to NULL.

Why the distractors fail:

• A and B are wrong because they claim a return value of 1; since the return expression involves the NULL parameter, it propagates NULL, not an integer.
• B and C are wrong because they claim one record was inserted; the NULL input prevents the conditional insert from firing.
• C correctly identifies the NULL return value but incorrectly assumes the insert succeeded.

Memory tip: Think of NULL as a "black hole" - anything it touches becomes NULL, and any condition it's part of fails to be TRUE (SQL uses three-valued logic: TRUE, FALSE, UNKNOWN). When in doubt with a NULL input, ask: "Does the procedure guard against NULL before inserting?" - if yes, zero rows and NULL return is the expected result.

No community discussion yet for this question.