Bird
0
0

You want to create a procedure that swaps two integer values using INOUT parameters a and b. Which of the following procedure bodies correctly swaps the values?

hard📝 Application Q15 of 15
SQL - Stored Procedures and Functions
You want to create a procedure that swaps two integer values using INOUT parameters a and b. Which of the following procedure bodies correctly swaps the values?
ABEGIN DECLARE temp INT; SET temp = a; SET a = b; SET b = temp; END
BBEGIN SET a = b; SET b = a; END
CBEGIN SET a = a + b; SET b = a - b; SET a = a - b; END
DBEGIN SET a = b; SET b = a + b; END
Step-by-Step Solution
Solution:

  1. Step 1: Understand swapping logic with INOUT parameters

    Swapping requires a temporary variable to hold one value while assigning the other.

  2. Step 2: Evaluate each option's correctness

    BEGIN DECLARE temp INT; SET temp = a; SET a = b; SET b = temp; END uses a temporary variable to swap correctly. BEGIN SET a = b; SET b = a; END overwrites a before assigning b. BEGIN SET a = a + b; SET b = a - b; SET a = a - b; END uses arithmetic swap but is valid only if no overflow and no NULLs. BEGIN SET a = b; SET b = a + b; END does not swap correctly.

  3. Final Answer:

    BEGIN DECLARE temp INT; SET temp = a; SET a = b; SET b = temp; END -> Option A

  4. Quick Check:

    Temporary variable swap = correct [OK]
Quick Trick: Use temp variable to swap INOUT parameters safely [OK]
Common Mistakes:
  • Overwriting variables without temp
  • Using arithmetic swap without checks
  • Ignoring NULL or overflow risks

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions
More SQL Quizzes
Advanced Window Functions - LEAD function for next row access - Quiz 12easy Common Table Expressions (CTEs) - Recursive CTE for hierarchical data - Quiz 8hard Database Design and Normalization - Star schema concept - Quiz 1easy Indexes and Query Performance - CREATE INDEX syntax - Quiz 14medium SQL Security Basics - Parameter binding mental model - Quiz 3easy Stored Procedures and Functions - WHILE loops in procedures - Quiz 3easy Transactions and Data Integrity - COMMIT and ROLLBACK behavior - Quiz 4medium Window Functions Fundamentals - ROW_NUMBER function - Quiz 6medium Window Functions Fundamentals - OVER clause with PARTITION BY - Quiz 3easy Window Functions Fundamentals - Window function vs GROUP BY mental model - Quiz 8hard