SSDT Database Unit Test: Checking a Simple INSERT Command

I have a very simple database for experiments with SQL Server unit tests. ClientsDB database contains just one table Clients with no records:

Clients table

and one stored procedure CreateClients:
CreateClient stored procedure

I have created a database project ClientsDatabase and a SQL Server unit test project ClientsDBTestProject to test my stored procedure. The autogenerated test (slightly formatted for readability) is shown below.


To create a simple test we must do:

  1. change assignments in first SELECT command and set test parameters such as client guid and name;
  2. delete the last SELECT command because the stored procedure does not return any value (there’s no RETURN statement in SP);
  3. insert a SELECT command before EXECUTE command to check that there’s no row with the test guid in Clients table;
  4. add a SELECT command after EXECUTE command to check that row with the test guid has inserted into Clients table;
  5. delete the autogenerated test condition inconclusiveCondition1;
  6. add first test conditions with type Row Count. Set the property Row Count to 0. Also you should check that property ResultSet is 1;
  7. add second test condition with type Row Count. Set the property Row Count to 1 and change ResultSet to 2.

Finally our unit test should look like this:

spCreateClientTest - final

Now we are ready to start our unit test.

  • Open Test Explorer (Test -> Windows -> Test Explorer)
  • Right-click dbo_CreateClientTest, then Run Selected Tests or simply click Run All because we have only one unit test.
  • If test has completed successfully, we see a green mark near the test name.
spCreateClientTest - success
Our test is working, everything seems OK. But…
Check the data in Clients table (thanks to SSDT, it can be made from Visual Studio now, or you can use SQL Server Management Studio).
select from Clients
 And now we see our test record. Is it funny? I think NO. This means that test data is not deleted after the test has been executed therefore the next time we run our test it would fail. Don’t forget about it!
It’s not a tragedy because SSDT has capabilities to solve this issue. I’ll write a post about it later.

Leave a Reply

Your email address will not be published. Required fields are marked *