Stored procedure testing in Testing Fundamentals - Test Execution Trace

Test Overview

This test runs a stored procedure that calculates the total sales for a given product ID. It verifies that the procedure returns the correct total amount.

Test Code - PyTest

Testing Fundamentals

import pyodbc
import unittest

class TestStoredProcedure(unittest.TestCase):
    def setUp(self):
        self.conn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=SalesDB;Trusted_Connection=yes;')
        self.cursor = self.conn.cursor()

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

    def test_total_sales_procedure(self):
        product_id = 101
        # Execute stored procedure
        self.cursor.execute("EXEC CalculateTotalSales ?", product_id)
        result = self.cursor.fetchone()
        self.assertIsNotNone(result, "No result returned from stored procedure")
        total_sales = result[0]
        # Expected total sales for product_id 101 is 1500.00
        self.assertEqual(total_sales, 1500.00, f"Total sales for product {product_id} should be 1500.00")

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

Execution Trace - 6 Steps

Step	Action	System State	Assertion	Result
1	Test starts	Test framework initializes test case for stored procedure	-	PASS
2	Database connection opens using pyodbc	Connected to SalesDB on localhost	-	PASS
3	Stored procedure 'CalculateTotalSales' is executed with product_id=101	Procedure runs on database server	-	PASS
4	Fetch the result from stored procedure execution	Result row with total sales value retrieved	Check that result is not None	PASS
5	Assert that total sales equals 1500.00	Compare returned value with expected	total_sales == 1500.00	PASS
6	Close database connection and end test	Resources released	-	PASS

Failure Scenario

Failing Condition: Stored procedure returns no result or incorrect total sales value

Execution Trace Quiz - 3 Questions

What does the test verify after executing the stored procedure?

A That the stored procedure returns the correct total sales value

B That the database connection is closed

C That the product ID is valid

D That the stored procedure runs without errors

Key Result

Always verify that stored procedures return expected results by asserting on their output values, and ensure test data in the database matches expected conditions.