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.
|
1 |
ALTER TABLE Clients NOCHECK CONSTRAINT CK_Clients_ClientName_Length; |
Alternatively, we can disable all constraints in a table with one command:
|
1 |
ALTER TABLE Clients NOCHECK CONSTRAINT ALL; |
Now our test really passed.
