Stored procedures can be difficult to test. The logic can be quite complex from time to time. So, how do you test them?
When I have a complex stored procedure, without respect to the kind of procedure being created (Insert, Update, Delete, Query), there are a few tips I have found that help validate that the procedure is working correctly.
If the procedure is against a large database, I will often create a mall data set against which to work, representing all the edge cases I am implementing. I can do that in a number of ways. I can use temp tables in a complete database, and copy a subset of records from actual tables. Another technique I like to use is to pass a temporary # Table, or user defined table type into the stored procedure. Using this technique, I can populate the temp table with representative data to exercise the logic of the procedure.
If the problem is that the data is too large to manage, and I have used a temp table method to allow the logic to be tested, this is probably not a permanent solution. You don’t want to insert into a temporary table llarge volumes of data. Moreover, the temp table will not allow the procedure to take advantage of indexes available on the real table. So, once you have the logic perfected, you need to return to drawing data from the permanent table.
The most important part of testing is to know what the procedure should do given specific input. If you pass a specific value, what is going to be the result? Once that is known, you cn perfect you procedure.
I like to test the procedure right in the procedure code. In the header I place in comments code to test the procedure. If it modifies the database, then I start a transaction, execute the procedure, evaluate if it worked correctly, and then roll the transaction back. I’ll provide an example of this technique tomorrow.
Cheers,
Ben