We came up with a new strategy: I should admit that even though it is yours truly who came-up with the idea, I am sure it came from above. I am not that smart to get something like this just like that
As the core-component cannot be tested directly, we decided to test the procedure directly: For all possible parameters which points to a column in the table we decided to execute both old and new versions of the procedure and compare the results. Unfortunately, the combination of parameters and the representation in the database turned the number of combinations to thousands.
To overcome the time needed for testing we decided to automate it. This is how we did that:
- Create two tables with the same set of columns. The columns name, data type and order should match the order of the output.
- Add additional columns to hold parameters.
- Add additional column to hold return value.
- Add an additional column for row id (we used bigint identity).
Return value and row id are the only columns which have NOT NULL set to true.
- Create a script which will do the following:
- Execute the old and new procedures for all possible parameter values and store the data with parameter values.
- Immediately update the table with the return value and parameters.
Finally compare the tables for any differences. SQL Compare or TableDiff could be used for this purpose.
- The signatures of the procedures should be identical. The names, order and data types of parameters (input) and results (output) of the new procedure should be identical to the old one.
- The results should be deterministic. That means the definition of the output should be static. Whatever the value of the parameters are the column names, data types and ordinal position should remain static.
So we concluded that the results were identical. We were able to produce the results and the management was convinced that we do not need additional QA testing. This saved weeks of QA efforts.
Do you think that you need the script which can do that? Unfortunately I do not have a script which can do that magic for all procedures but I am ready to help you in creating one to meet your needs. Please drop me a note :)