How would you use stored procedures or database functions in a QA context?

QA-specific uses of stored procedures:

1. Test data factory — one call to set up a complete scenario:

-- Stored procedure: creates a customer + order + payment in one atomic call
CALL create_test_order(
  p_email      => 'test@example.com',
  p_amount     => 99.99,
  p_status     => 'completed',
  OUT p_order_id
);

This is simpler than three separate INSERTs in every test and ensures consistency.

2. Validating business logic in the DB layer: Some apps put logic in stored procedures (price calculations, discount rules). QA should test these directly with different input combinations rather than only via the UI.

3. Cleanup procedures:

CALL cleanup_test_data('test-run-abc123');
-- Internally: DELETE FROM orders WHERE ref LIKE 'test-run-abc123%'; etc.

Trade-offs:

• Stored procedures are harder to version-control and review than application code.
• Changes to procedures can break tests silently — add them to the test coverage scope.
• Debugging a failing procedure requires DB tooling, not just a test framework debugger.

When to avoid: If the test framework already has a clean data-builder API and transaction rollback, adding stored procedures is extra complexity. Prefer application-layer builders and reserve stored procedures for scenarios where the DB logic is the thing under test.