SQL - Stored Procedures and Functions
You want to create a procedure that calculates the total price including tax. It takes price and tax rate as inputs and returns the total price. Which is the correct way to define the procedure?
You want to create a procedure that calculates the total price including tax. It takes price and tax rate as inputs and returns the total price. Which is the correct way to define the procedure?
hard📝 Application Q8 of 15
Step-by-Step Solution
Solution:
Step 1: Identify correct parameter usage
Input parameters use IN, output parameters use OUT to return values.Step 2: Check assignment syntax
SET is required to assign values to variables or OUT parameters inside BEGIN-END.Step 3: Evaluate each option
CREATE PROCEDURE CalcTotal(IN price DECIMAL, IN taxRate DECIMAL, OUT total DECIMAL) BEGIN SET total = price + (price * taxRate); END; correctly uses IN and OUT with SET. CREATE PROCEDURE CalcTotal(price DECIMAL, taxRate DECIMAL) BEGIN RETURN price + (price * taxRate); END; wrongly uses RETURN. CREATE PROCEDURE CalcTotal(IN price DECIMAL, IN taxRate DECIMAL) BEGIN SELECT price + (price * taxRate) AS total; END; returns a SELECT result, not an OUT parameter. CREATE PROCEDURE CalcTotal(IN price DECIMAL, IN taxRate DECIMAL, OUT total DECIMAL) BEGIN total = price + (price * taxRate); END; misses SET keyword.Final Answer:
CREATE PROCEDURE CalcTotal(IN price DECIMAL, IN taxRate DECIMAL, OUT total DECIMAL) BEGIN SET total = price + (price * taxRate); END; -> Option BQuick Check:
Use IN, OUT and SET for output in procedures [OK]
Quick Trick: Use OUT params and SET to return values from procedures [OK]
Common Mistakes:
- Using RETURN instead of OUT
- Assigning without SET
- Returning SELECT instead of OUT param
Master "Stored Procedures and Functions" in SQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore SQL Quizzes