Test-Driving Stored Procedures in SQL Server in VS2008

At my Tampa Code Camp presentation Saturday, one of the things I briefly touched on was test-driving stored procedures in SQL Server using Visual Studio 2008. Given that the nature of databases usually prohibits most teams from diving down this path, I wanted to dive more into this very cool feature.

A little background first. In VS2005, a product was released called Team System for Database Professionals. The two key features it enabled was an object view of your database schema so that the “truth” of your database could be moved out of the DB and into source control, and unit testing of Stored Procedures. This continues in VS2008, and we’ll combine the two features to test–drive our stored procs and table definitions.

First, let’s start with our scenario. We have an application which requires users to login. We need a simple stored procedure which returns whether the username and password specified map to a valid, active user. We are starting from scratch, so let’s fire up Visual Studio and create a new blank solution called DatabaseLogin:

Since we are dealing with a database, we also need to do some prep work there. We need the database created that we’ll be driving the creation of objects in. From Visual Studio, open up Server Explorer, right-click on Data Connections, and say “Create new SQL Server Database…”:

Enter your credentials and name the database “Login”:

Now we have our blank solution and our database. Feel free to expand out the database nodes and see that all we have is a blank, empty database.

The way we work with databases is similar to how we’ll work with code. We’ll make changes, “compile” it, and then test it. The projects we use will be slightly different, and let’s take a look at the first one. In our solution, add a new Microsoft SQL Server 2005 project and call it LoginDB:

You’ll see other options on there as well. The Wizard is handy if you have an existing database and want to import the schema into your project. But we can just start with the regular project since we will be creating it as we go.

(Note: If you don’t see the DB project, you need Team Suite installed or the Database Professional’s edition)

One more setup piece – right click on our LoginDB project and go to the properties. Click on the “Build” tab, and set the target connection to be the database we setup at the beginning of this:

Also, be sure to uncheck “Block incremental deployment if data loss might occur” – this would be an important option for going to a real database, but for dev purposes, we want to have everything wide open. You can now close the property pages.

Now we can get down to business. Let’s add a new Test Project to start testing our database. Right click on our solution, and add a new Test Project called LoginDBTests:

Once it is created, delete out the AuthoringTests.txt, ManualTest1.mht and UnitTest1.cs files that are auto-created. Now right-click on LoginDBTests and go to Add->New Test…->Database Unit Test and call it BlankUsernameFails.cs:

That will add a bunch of references and bring up the connection dialog box. Change the “Execute Unit Tests” connection to the Login connection we setup earlier. Note we could use a secondary connection to validate the tests – for example, if we wanted to run the tests as a regular user, but validate results using sa. Also on this dialog, check to Automatically deploy the database project, and choose the project we selected earlier. Also pick the Deployment configuration as Default and then click Ok:

Note that the very last option allows us to generate test data before the tests are run. There are some neat data generation features we could get into, but we’ll stick with a more simplistic approach for now.

When you clicked Ok, that brings up the Script Editor. Click the link to create, and you’ll see that we are now in a T-SQL editor. This is where we are going to compose our tests. As the title shows, what we want to test is that when we call our stored procedure with a blank username, it does not return that the user is valid. So we’ll need to do two things. First, call our stored proc:

Second, change out Test Conditions to expect that the value returned is false – in other words, our Stored Proc should return true/false as to whether the user is logged in. We do this by deleting the existing inconclusive condition, adding a new scalar value condition, and setting the value to be true:

Now with out test written, let’s run it. Either go to the Test->Run->Tests in Current Context or hit Ctrl->R and then T. We’ve got a failing test!

If we look at the failure message, we see that sp_login doesn’t exist. So this is more of a compile type error than a true failing test. But we can fix it nonetheless. Click on your LoginDB project and in the main menu go to View->Schema View. You should see the Schema View come up:

Now, right-click on Stored Procedures, and go to Add->Stored Procedure…. You’ll see the Add new item dialog box come up – name it sp_login:

This now brings up our sp_login stored procedure. Note that at this point nothing has been added to the database – we are purely working offline. You can verify this by going back to Server Explorer and looking at the database. But let’s rerun our unit test and see what happens. Go to Test->Run->Tests in Current Context. Our test still fails, but what’s the error this time?

Our stored procedure exists! This is because we told our unit tests to automatically deploy our database project before running the tests, so it did, and now the stored procedure exists in the database!

However, it isn’t right, so let’s modify the stored procedure to match what our unit testing is calling it to do:

And when we rerun our tests:

It passes! It doesn’t do much, but we’ve got the flow going now. Our next step is to see if we can get it to pass if a valid username and password are passed in. So go back to our LoginDBTests and add a new Database Unit Test called ValidUsernamePasswordForActiveUserPasses.cs. Following the 3 A’s of writing unit tests (Arrange, Act, Assert), let’s arrange our data for our stored procedure:

So we have a table, login, which has some columns – two strings and a bit field. For the “Assert” part of our 3 A’s, add a scalar result of “true”:

Note that even though is_active is a bit field, and that’s really the field we want returned, we are setting the Expected value to “true”. That’s because the unit testing framework behind the scenes maps bit fields to true/false, so if we expected “1” it would fail that it couldn’t convert the value.

Speaking of failing, let’s see what happens when we run this test:

Our test fails because the object “login” doesn’t exist. But before we dive into that, notice that the “Test Name” for both of our tests is the same. I’m not sure why it isn’t displaying the name of our tests, and trying to rename it using the rename button won’t let me name it the existing file. Something I’ll have to look into.

Anyway, back to the red test at hand. We need a login table. If you’ve made it this far, you know the next steps. Go to our LoginDB project, go to the Schema View, and Add a table called login with the appropriate columns:

< /a>

Let’s rerun our tests. They fail, but this time with “Failed DatabaseTest1 LoginDBTests ScalarValueCondition Condition (UserIsValid) Failed: ResultSet 1 Row 1 Column 1: values do not match, actual ‘false’ expected ‘true’ “. Progress! Now we know everything is setup, or our tests “compile” and we get to change real business logic. I also want to point out again that the tables have been deployed to the database, which speeds up this process.

Now, let’s get that test passing! We’re only on the red of our red-green-refactor! Open up sp_login, and change it to make the test pass:

And rerun our tests:

Our test failed! Aha, but it didn’t. If you look closely, the failing test was using “UserValidIsFalse” which was the condition from our first test. In other words, we passed our valid user test, but broke our earlier test. That is because our select statement doesn’t return any rows if no conditions match. So let’s fix that:

And run our tests:

All green! Ok, 2/3rds of the way done – we’ve done Red and Green, so let’s refactor. Our Unit tests provide two ways to refactor those setup scripts. We can either run them local to the test by using pre and post scripts:

Or we can add it to the Common Scripts section so that it will run for all DB Unit tests:

For now, let’s move our setup scripts to the Pre-test phase until we have more tests that use it. To do this, open up the Valid Username test, highlight the Truncate and Insert statements, and cut them. Then select the drop down that says “Test” and pick Pre-Test from it. Click to create the script, and paste in our setup scripts:

So our Test is now just the exec script. Let’s rerun our tests:

And everything still passes!

So hopefully that gives you a good understanding of how the VS tools can help you test-drive even your database development. While the process is going to be slower, it is nonetheless valuable, and moves the definition of your database out of your scripts and into a source-controlled, object-based definition, with unit tests and all.

The only downside is that it does require you to have the Team Edition for Database Professionals, but since Unit Testing moved to the VS Professional edition with the release of VS2008, perhaps this will make it down at some point too.

Happy testing!