Stored procedure testing in Testing Fundamentals - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a stored procedure in a database?

A stored procedure is a set of SQL statements saved in the database that can be executed as a single unit. It helps automate repetitive tasks and improves performance.

beginner

Why do we test stored procedures?

We test stored procedures to ensure they work correctly, return expected results, handle errors properly, and do not cause data issues.

beginner

Name one common type of test performed on stored procedures.

Functional testing: checking if the stored procedure returns the correct data for given inputs.

beginner

What is a test case for stored procedure testing?

A test case is a set of inputs, execution steps, and expected results used to verify that the stored procedure behaves as intended.

intermediate

How can you check if a stored procedure handles errors correctly?

By providing invalid or unexpected inputs during testing and verifying that the procedure returns error messages or handles the situation gracefully without crashing.

What is the main purpose of testing a stored procedure?

A To verify it returns correct results

B To change its code

C To delete data

D To create new tables

Testing ensures the stored procedure returns the correct results for given inputs.

Which of the following is NOT a typical test for stored procedures?

A Functional testing

B Performance testing

C Syntax highlighting

D Error handling testing

Syntax highlighting is a code editor feature, not a test type for stored procedures.

What should you do if a stored procedure returns unexpected results during testing?

A Ignore it

B Run it again without changes

C Delete the procedure

D Report and fix the issue

Unexpected results indicate a problem that should be reported and fixed.

Which input type is useful to test error handling in stored procedures?

A Invalid or unexpected inputs

B Valid inputs

C Empty inputs only

D No inputs

Invalid or unexpected inputs help verify if error handling works correctly.

What is a test case in stored procedure testing?

A A saved procedure

B A set of inputs and expected results

C A database backup

D A SQL command

A test case defines inputs, steps, and expected results to check procedure behavior.

Explain why testing stored procedures is important and what key aspects should be tested.

Think about what can go wrong if stored procedures are not tested.

Describe how you would create a test case for a stored procedure that calculates discounts.

Consider inputs like customer type or purchase amount.

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