Every developer that I know hates to test his or her code. We know we are good enough that once the code compiles it has to be correct, right?
Well not so fast…..
What if I could show you a way to improve the quality of your deliverable code and have piece of mind that the code you just wrote or changed did not break other code in the system.
Interested now?
By employing a Test Driven Development (TDD) method to simplify and automate the testing of SQL Procedural code, you can have proven code-quality in both functionality and syntax. You may even find performance issues before the code ever gets to Quality Assurance; besides, they are not considered defects if they are caught and fixed before QA gets the code. A little work upfront can reap large rewards further down the line in the code quality and less time spent reviewing or debugging code.
What tools will I need?
Our TDD tool of choice is an open source add-in called tSQLt. This tool allows you to set up your test data and expected results in a reusable script that can be run after creation or a change in logic of your code. If the actual results do not match the expected results it identifies it quickly for you. Having the test data in a script ensures that when data changes in the actual tables it does not affect your test or outcomes. Each separate test is part of a Class (i.e. schema) of tests, and you are able to run a single test, an entire class of tests, or all tests with a single command.
Sounds Easy! How about some details.
Each individual test has three main parts: Define, Execute and Assert.
In the Define section is where you define your test data and your expected results. tSQLt will mimic real tables with temporary testing tables, and populate them with the test data as defined by you. You can define as many testing tables and as much data as you need to test your specific piece of code.
The Execute section is where the call to the actual code you want to test is made. This is just normal SQL code to call a stored procedure.
The Assert section is where you are able to compare the actual results with the expected results that you laid out in the Define section. tSQLt will list out the pass or fail status of each test along with differences between the actual and expected if the test did not pass. This is all great theory, but how do you actually use this while doing development? It’s not that hard, just the reverse of how many developers develop now.
How do I get started?
Before you write your first line of code, you define your test. Define what data you need to start with as well as what data you expect to end up with. This is the first of many benefits as it assures that you have a complete understanding of the functionality for the code that you are about to write before you start. With this data you can then create your test (and class if needed) and complete the Define section.
Now that you know what data you are starting with and what data you are hoping to end with, you can name the procedure that you are going to write. Every organization that I have ever worked with has had a naming convention that they follow. Some are better than others, but it is always nice to have a standard. Devise the name and use it to write the code to execute it in the Execute section.
In the Assert section you can compare your results with your expected results. Your results can be variable-to-variable comparisons, or table-to-table comparisons. Each comparison will be its own assertion and each assertion will create its own result.
What, You want to see a real world example?
A sample test script would look like this:
In the Declare section we declare a variable named @Price that we will use after will populate later and then compare in the assert column. We then make a fake table that mimics the Cake table. tSQLt creates it without any constraints so you can populate it with only the data you need. We then create a table to hold the expected results and populate that.
In the Execute section we call a procedure to get the price of the cake specified and populate the @Price variable. Executing the updateCakeColor procedure will update the color of any cake in the cake table to White where it is set to the color passed in the parameter (we ran out of Yellow food coloring). We then select the rows from the updated table into an #ACTUALVALUES temporary table that we can use to compare in the next step. Notice we sort the rows so they have a chance of matching our expected results
In the Assert Section we then compare the hardcoded total with the price that we retrieved in the Execute section. This will either pass or fail depending if the numbers are equal or different. We then compare the entire contents of the #ACTUALVALUES table with the #EXPECTEDVALUES table. If the entire contents of the two tables match then this assertion will pass, otherwise it will fail.
These two tests could be broken up into two separate scripts for ease of maintenance, but for ease of demonstration I have combined them. If they were separated, you would add them into the same class (schema) and you could run the entire class as a single test.
The best part?
As you finish writing each procedure, you can then test and retest it with known input and expected outputs with a single command. Of course when your individual test passes, you would always do a full run of your entire test collection. Doing this will provide you the comfort of knowing that your code is working as expected and any changes have not had an adverse effect on any other code in the repository.
This is where you sit back and have some tea while you wait.
