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.