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