Author: AZ

I use the steps described in the MSDN article How to: Create Test Conditions for the SQL Server Unit Test Designer and found some variations while repeating it in Visual Studio 2013.

1) in step 5 we can use a newer version of dll if we have a newer version of SQL Server installed on local machine. Then number 110 in the path means SQL Server 2012, 120 – SQL Server 2014.

2) in step 8 we need to change the VS version number from 10.0 to 12.0. So you need to add these lines:

Hint: import of Microsoft.CSharp.targets is located at the end of the file.

“SSDT now enables you to develop, debug and execute database unit tests interactively in Visual Studio…”

1) enable SQL debugging on test project:

• right-click test project in Solution Explorer, then click Properties
• click Debug
• check the option Enable SQL Server debugging

2) increase execution timeout:

• double-click on app.config in Server Explorer

• set CommandTimeout in the line ExecutionContext to a greater value (for example, 300 seconds)

• rebuild the test project to apply changes in app.config

3) set breakpoint in stored procedure we are debugging. You can open it in your database project or via SQL Server Object Explorer (View -> SQL Server Object Explorer).

4) start the debugging:

• open Test Explorer;
• right-click the test
• click Debug Selected Tests

Immediately after that you will see the debug panels in Visual Studio. But it’s too early to press any buttons to step into or over. You should wait some time while Visual Studio loads and runs all the actions before it reach the breakpoint. At this time the breakpoint is shown as a white circle with a red rim.

When the execution stops at breakpoint the line will be highlighted with yellow color. Now you can step forward over the commands of the stored procedure being debugged.

When I explored the negative unit testing (see here) I encountered a new property MatchFirstError in ExpectedSqlException.

According to MSDN, true value means that the expected severity, state, and error number must match to the first SqlError in the exception; false value – to any SqlError in the exception.

I was wondering in which situations a SQL error could not be the first. If it’s not the first, it can be the second error and so on. The next question – how can I raise two errors?
When SQL server executes T-SQL commands, the error interrupts that batch and raises an exception. So this error will be the the first and the only error.
But when we use try – catch blocks, there can be errors both in try and catch block. Therefore there can be multiple errors. It can be check in a very simple way.
We execute a code that tries to delete non-existing stored procedures in try and catch block.

The result is two(!) errors from one stored procedure.

Let’s return back to MatchFirstError property. The default value (true) means that expected error will be the only exception or the cause of other consequent exceptions (in another words, it won’t be raised by another error).

In one of my previous post I’ve faced with exception that occurred during unit test. Placing an appropriate command to disable constraints helps to avoid exception. But what should we do if we want to check that T-SQL code really fires an exception? The answer is very simple – we need to write a test that will handle the exception. This technique is called “negative testing”.

I will use the same example that I described in my previous test.

• I change the Pre-test section to enforce constraints. Just place this command:

• open the C# code of unit test: right-click the test in Solution Explorer, then click View Code

• find the definition of our test (search for string dbo_CreateClientTest)
• change the line in square brackets above the definition by adding a ExpectedSqlException attribute

Message number and severity code was taken from the output of failed test that was run before. All possible properties that can be used in ExpectedSqlException declaration are explained here.
Note: The test will work even if you omit all the properties but using the exact values gives you the better level of granularity to check the right kind of exceptions.

When we run a piece of T-SQL code, it can be dependent on another T-SQL code such as function. For example, stored procedure queries data with only one SELECT command. In this command you can use scalar functions in SELECT clause, table-valued functions or views in FROM clause, scalar functions in WHERE clause (that’s a very bad habit, but it’s possible), etc.

Our T-SQL code can be affected by other database objects such as constraints and triggers. When we insert or update data, these objects can prevent to enter incorrect values, so our command would fail.

Let’s look at how they would impact on database unit testing.
I modify the test case I’ve created earlier in this post and add a simple constraint.

My test passed because ClientName (‘John Smith’) consists of 10 chars. Now I change to something shorter, and the test failed.

There’s no error in stored procedure I’ve been testing, it’s a violation of constraint. So to pass this test (I mean to test the functionality of stored procedure) I should eliminate constraint from being applied during the test. It can be done in the following manner:

• choose an item Pre-test in the dropdown list shown below

• Click the link Click here to create in the center of Test Designer.
• Write T-SQL code to disable the constraint.

Alternatively, we can disable all constraints in a table with one command:

Now our test really passed.