I have a very simple database for experiments with SQL Server unit tests. ClientsDB database contains just one table Clients with no records:
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:
- change assignments in first SELECT command and set test parameters such as client guid and name;
- delete the last SELECT command because the stored procedure does not return any value (there’s no RETURN statement in SP);
- insert a SELECT command before EXECUTE command to check that there’s no row with the test guid in Clients table;
- add a SELECT command after EXECUTE command to check that row with the test guid has inserted into Clients table;
- delete the autogenerated test condition inconclusiveCondition1;
- add first test conditions with type Row Count. Set the property Row Count to 0. Also you should check that property ResultSet is 1;
- 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:
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.