Stored procedure testing in Testing Fundamentals - Build an Automation Script

Verify stored procedure returns correct employee details by ID

Preconditions (2)

Step 1: Execute stored procedure 'GetEmployeeById' with input parameter 101

Step 2: Capture the output result set

Step 3: Verify the output contains employee details with ID 101

Step 4: Verify the employee name is 'John Doe'

Step 5: Verify the employee department is 'Sales'

✅ Expected Result: Stored procedure returns a single record with employee ID 101, name 'John Doe', and department 'Sales'

Automation Requirements - Python unittest with pyodbc

Assertions Needed:

Verify output record count is 1

Verify employee ID equals input parameter

Verify employee name matches expected

Verify employee department matches expected

Best Practices:

Use parameterized queries to call stored procedure

Use explicit connection and cursor management

Close database connections after test

Use assertions to validate each output field

Handle exceptions and fail test if errors occur

Automated Solution

Testing Fundamentals

import unittest
import pyodbc

class TestStoredProcedure(unittest.TestCase):
    def setUp(self):
        self.conn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=TestDB;UID=user;PWD=password')
        self.cursor = self.conn.cursor()

    def tearDown(self):
        self.cursor.close()
        self.conn.close()

    def test_get_employee_by_id(self):
        employee_id = 101
        expected_name = 'John Doe'
        expected_department = 'Sales'

        # Call stored procedure with parameter
        self.cursor.execute("EXEC GetEmployeeById ?", employee_id)
        row = self.cursor.fetchone()

        # Assert one record returned
        self.assertIsNotNone(row, 'No data returned from stored procedure')

        # Assert employee ID matches
        self.assertEqual(row.EmployeeID, employee_id, f'Expected EmployeeID {employee_id}')

        # Assert employee name matches
        self.assertEqual(row.Name, expected_name, f'Expected Name {expected_name}')

        # Assert employee department matches
        self.assertEqual(row.Department, expected_department, f'Expected Department {expected_department}')

if __name__ == '__main__':
    unittest.main()

The setUp method opens a database connection and cursor before each test.

The tearDown method closes them after each test to avoid resource leaks.

The test test_get_employee_by_id calls the stored procedure using a parameterized query to prevent SQL injection.

It fetches one row and asserts it is not None, meaning data was returned.

Then it checks the employee ID, name, and department fields match expected values.

Using unittest assertions helps clearly report failures.

This structure ensures clean setup, execution, validation, and cleanup for reliable stored procedure testing.

Common Mistakes - 4 Pitfalls

Hardcoding SQL query with string concatenation instead of parameters

Not closing database connections and cursors after tests

Not checking if stored procedure returned any data before accessing fields

Using print statements instead of assertions for validation

Bonus Challenge

Now add data-driven testing with 3 different employee IDs and expected results

Show Hint

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