I have had many occasions recently where I have needed to maintain complex stored procedures and functions. These were broken already, usually in fairly subtle ways - there were very few occasions where you would call the SP with valid parameters and it just plain didn't work.
My solution was to evolve a framwork that executes the stored procedures inside a transaction, after initializing the database to the initial conditions I needed, then testing for the expected result, also within the same transaction. The transaction was rolled back at the end of the test.
This has worked very well. But some would call this "integration testing" since it involves integrating with the database. I call it unit testing, since I tested individual components and individual test cases for those components, and since I completely controlled the initial state of the database.
But where should the line be drawn? Is this integration testing or unit testing? Is there a practical reason why this sort of test is a bad idea? If this is "only" integration testing, does anyone have suggestions on how to do actual "unit tests" on these stored procedures?