Examine the employee table structure: Which set of statements immediately returns empname for a given emp_id by using a parameterized prepare statement? A. B. C. D.

Question

Exhibits

Options

• ADELIMITER // PREPARE procedure proc() BEGIN PREPARE V_ename VARCHAR(45); PREPARE prepStmt FROM 'SELECT empname INTO v_ename FROM employee WHERE emp_id = ?'; SET @v1=1; EXECUTE prepStmt USING @v1; SELECT v_ename; END // DELIMITER ;
• BSET @num='SELECT empname FROM employee WHERE emp_id = 1'; PREPARE prepStmt FROM @num; EXECUTE prepStmt;
• CPREPARE prepStmt FROM 'CREATE OR REPLACE VIEW ev AS SELECT empname FROM employee WHERE emp_id = ?'; SET @num=1; EXECUTE prepStmt USING @num;
• DPREPARE prepStmt FROM 'SELECT empname FROM employee WHERE emp_id = ?'; SET @num=1; EXECUTE prepStmt USING @num;

• A
  8% (4)
• B
  12% (6)
• C
  2% (1)
• D
  79% (41)

Explanation

Option D correctly uses the three-step parameterized prepared statement pattern: PREPARE defines the query template with a ? placeholder, SET assigns a value to a user variable, and EXECUTE ... USING binds that variable to the placeholder and immediately returns the result set - clean, dynamic, and directly usable.

Option A is syntactically invalid - PREPARE procedure proc() and PREPARE V_ename VARCHAR(45) misuse the PREPARE keyword, which is only for SQL statement templates, not procedure declarations or variable declarations (CREATE PROCEDURE and DECLARE are the correct keywords for those respectively).

Option B is a valid prepared statement but not parameterized - it hardcodes emp_id = 1 inside the string assigned to @num, defeating the purpose of parameterization; @num here holds the query text, not a bound parameter value.

Option C uses a ? placeholder correctly but prepares a CREATE OR REPLACE VIEW DDL statement, which creates a database object rather than returning data - it never directly returns empname.

Memory tip: Think P-S-E - Prepare with ?, Set your variable, Execute USING it. Any option that skips the ? placeholder or doesn't use EXECUTE ... USING is not a true parameterized statement.

No community discussion yet for this question.