Stored procedure testing in Testing Fundamentals - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple stored procedure call

Consider the following stored procedure that returns the total count of employees in a company.

What will be the output of calling EXEC GetEmployeeCount; if the Employees table has 150 rows?

Testing Fundamentals

CREATE PROCEDURE GetEmployeeCount AS BEGIN SELECT COUNT(*) AS TotalEmployees FROM Employees; END;

A TotalEmployees = 0

B No output

C Syntax error

D TotalEmployees = 150

💡 Hint

The procedure counts all rows in the Employees table.

The procedure runs a SELECT COUNT(*) query on the Employees table, which has 150 rows, so it returns 150 as TotalEmployees.

📝 Syntax

intermediate

Identify the syntax error in stored procedure

Which option contains a syntax error when creating a stored procedure that inserts a new employee?

Testing Fundamentals

CREATE PROCEDURE AddEmployee @Name VARCHAR(50), @Age INT AS BEGIN INSERT INTO Employees (Name, Age) VALUES (@Name, @Age); END;

A CREATE PROCEDURE AddEmployee @Name VARCHAR(50), @Age INT AS BEGIN INSERT INTO Employees (Name, Age) VALUES (@Name, @Age); END;

B CREATE PROCEDURE AddEmployee @Name VARCHAR(50), @Age INT BEGIN INSERT INTO Employees (Name, Age) VALUES (@Name, @Age); END;

C CREATE PROCEDURE AddEmployee @Name VARCHAR(50), @Age INT AS INSERT INTO Employees (Name, Age) VALUES (@Name, @Age);

D CREATE PROCEDURE AddEmployee (@Name VARCHAR(50), @Age INT) AS BEGIN INSERT INTO Employees (Name, Age) VALUES (@Name, @Age); END;

💡 Hint

Check for missing keywords or misplaced syntax in procedure declaration.

Option B is missing the AS keyword before BEGIN, which is required in stored procedure syntax.

❓ optimization

advanced

Optimizing a stored procedure for performance

A stored procedure retrieves all orders for a customer by joining Orders and Customers tables. Which option improves performance by reducing unnecessary data retrieval?

Testing Fundamentals

CREATE PROCEDURE GetCustomerOrders @CustomerID INT AS BEGIN SELECT * FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.ID WHERE Customers.ID = @CustomerID; END;

A SELECT Orders.* FROM Orders WHERE CustomerID = @CustomerID;

B SELECT * FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.ID;

C SELECT Customers.*, Orders.* FROM Customers LEFT JOIN Orders ON Customers.ID = Orders.CustomerID WHERE Customers.ID = @CustomerID;

D SELECT * FROM Orders WHERE CustomerID = @CustomerID;

💡 Hint

Avoid joining tables if not needed and select only required columns.

Option A selects only Orders columns and filters by CustomerID without joining Customers table, reducing data processed and improving performance.

🔧 Debug

advanced

Debugging a stored procedure with parameter issues

A stored procedure is supposed to update an employee's salary but does not change any data when called. Which option explains the likely cause?

Testing Fundamentals

CREATE PROCEDURE UpdateSalary @EmpID INT, @NewSalary DECIMAL(10,2) AS BEGIN UPDATE Employees SET Salary = @NewSalary WHERE EmployeeID = @EmpID; END;

A The Employees table does not have a Salary column.

B The procedure is missing a COMMIT statement after UPDATE.

C The parameter names in the procedure call do not match those in the procedure definition.

D The procedure lacks a WHERE clause, so no rows are updated.

💡 Hint

Check if the parameters passed when calling the procedure match the defined names.

If the procedure is called with incorrect parameter names or order, the UPDATE may not affect any rows because the filter condition fails.

🧠 Conceptual

expert

Understanding transaction handling in stored procedures

Which option best describes what happens if a stored procedure contains multiple UPDATE statements inside a transaction and one UPDATE fails?

A All previous successful UPDATEs are rolled back, leaving the database unchanged.

B Only the failed UPDATE is rolled back; previous UPDATEs remain committed.

C The transaction commits all changes regardless of failure.

D The procedure stops but changes before failure remain uncommitted and visible.

💡 Hint

Think about atomicity in transactions.

Transactions ensure all-or-nothing behavior; if one UPDATE fails, the entire transaction rolls back to maintain data integrity.

Practice

1. What is the main purpose of testing a stored procedure in a database?

easy

A. To verify that the procedure performs the expected operations correctly

B. To create new tables in the database

C. To delete all data from the database

D. To change the database schema automatically

Solution

1. Step 1: Understand stored procedure testing goal

   Testing stored procedures means checking if they do what they are supposed to do.

2. Step 2: Identify correct purpose

   Creating tables, deleting data, or changing schema are not testing goals but database operations.

3. Final Answer:

   To verify that the procedure performs the expected operations correctly -> Option A

4. Quick Check:

   Testing = Verify expected operations [OK]

Hint: Testing checks if procedure works as expected [OK]

Common Mistakes:

• Confusing testing with creating or deleting data
• Thinking testing changes database structure
• Assuming testing runs automatically without checks

2. Which SQL command is used to execute a stored procedure named GetUserData with an input parameter userId?

easy

A. EXECUTE GetUserData userId;

B. RUN PROCEDURE GetUserData userId;

C. START GetUserData(userId);

D. CALL GetUserData(userId);

Solution

1. Step 1: Recall correct syntax for calling stored procedures

   The standard SQL command to run a stored procedure is CALL procedure_name(parameters);

2. Step 2: Match syntax with options

   Only CALL GetUserData(userId); uses CALL with parentheses and parameter correctly.

3. Final Answer:

   CALL GetUserData(userId); -> Option D

4. Quick Check:

   CALL + procedure + (params) = correct call [OK]

Hint: Use CALL with parentheses to run procedures [OK]

Common Mistakes:

• Using EXECUTE without parentheses
• Using RUN PROCEDURE which is invalid syntax
• Forgetting parentheses around parameters

3. Consider this stored procedure call:
CALL CalculateDiscount(100, 0.1);
If the procedure returns the discounted price by subtracting the discount (price * rate), what is the expected output?

medium

A. 10

B. 110

C. 90

D. 100

Solution

1. Step 1: Understand inputs and calculation

   Price is 100, discount rate is 0.1 (10%). Discount amount = 100 * 0.1 = 10.

2. Step 2: Calculate discounted price

   Discounted price = 100 - 10 = 90.

3. Final Answer:

   90 -> Option C

4. Quick Check:

   100 - (100*0.1) = 90 [OK]

Hint: Subtract price * rate from price for discount [OK]

Common Mistakes:

• Adding instead of subtracting discount
• Using discount rate as final price
• Confusing discount amount with discounted price

4. You have this stored procedure call:
CALL UpdateUserEmail(123, '[e-mail]');
But it returns an error: ERROR: procedure UpdateUserEmail does not exist.
What is the most likely cause?

medium

A. The procedure name is misspelled or does not exist

B. The email format is invalid

C. The user ID 123 does not exist

D. The database connection is lost

Solution

1. Step 1: Analyze error message

   The error says the procedure does not exist, which means the database cannot find a procedure named UpdateUserEmail.

2. Step 2: Identify likely cause

   This usually happens if the procedure name is wrong or it was not created.

3. Final Answer:

   The procedure name is misspelled or does not exist -> Option A

4. Quick Check:

   Error about procedure existence = name or creation issue [OK]

Hint: Check procedure name spelling and existence first [OK]

Common Mistakes:

• Assuming input data causes procedure not found error
• Blaming connection issues without checking procedure
• Ignoring error message details

5. You want to test a stored procedure CalculateTax that takes an income amount and returns tax owed.
The procedure has a bug: it returns NULL for zero income instead of 0.
Which test case will best detect this bug?

hard

A. Call CalculateTax(1000) and check if result is positive

B. Call CalculateTax(0) and check if result is 0

C. Call CalculateTax(-100) and check for error

D. Call CalculateTax(NULL) and check for NULL

Solution

1. Step 1: Understand the bug

   The procedure returns NULL instead of 0 when income is zero.

2. Step 2: Choose test case to detect bug

   Testing with zero income and expecting 0 will reveal if NULL is returned incorrectly.

3. Final Answer:

   Call CalculateTax(0) and check if result is 0 -> Option B

4. Quick Check:

   Test zero input to catch NULL instead of 0 bug [OK]

Hint: Test edge inputs like zero to find null bugs [OK]

Common Mistakes:

• Testing only positive values misses zero case
• Testing negative income when not required
• Assuming NULL input tests zero income bug