I have been following your series and it was actually the inspiration for finally taking the plunge with SQL Test.

I was trying to refactor my Expected and Actual table creation to the SetUp method but after doing so all my tests are broken and the output shows that the [BoardSummary].[Actual] table does not exist... there are other similar failures on all my tests. It looked to me like the method simply wasn't being called. The class I've got for all my tests is BoardSummary, I created a new test in the class then renamed it to SetUp so I'm confident it is in the right class - but either I've massively mucked up the setup method or it isn't being called before the tests... I'm not sure.

SET @AreaID = 374 -- Leicester City PCT SET @StartDate = '2012-01-01 00:00:00' SET @EndDate = '2012-12-31 23:59:59' SET @ProviderList = N'TEST|' SET @RootReportLevelID = 380 -- The SHA to constrain results to just this area

SET @AreaID = 374 -- Leicester City PCT SET @StartDate = '2012-01-01 00:00:00' SET @EndDate = '2012-12-31 23:59:59' SET @ProviderList = N'TEST|' SET @RootReportLevelID = 380 -- The SHA to constrain results to just this area

I'm glad my tutorials have inspired you. The difficulty is that once you start using TDD, you'll struggle to work without it.

NB: Odd, I didn't see the refactored code when I first looked

Looking at the example code you posted, I'm assuming that this is before refactoring creation of the actual and expected table out into the setup method. The most obvious thing to check is that you also remove the DROP TABLE statements at the beginning of [BoardSummary].[test GetBoardSummary_2ScorecardsAdded_MaxMinUsedForRanges]. In fact arguably the DROPS aren't really required since the CREATES get rolled back at the end of each test. It's my OCD that makes me do it in my code

If that's not the problem, try creating a [BoardSummary].[SetUp] that just does RAISERROR('Setup called!', 0, 1) WITH NOWAIT; then run just one test tSQL.Run '[BoardSummary].[test GetBoardSummary_2ScorecardsAdded_MaxMinUsedForRanges]' in another window to see if SetUp does actually get called. Then build things up from there.

Another thing to check (although a longshot) if you happen to be running on a case-sensitive database is that you are running the latest version of the tSQLt framework SELECT * FROM tSQLt.Info() should yield 1.0.4504.21220. There was a small issue in an earlier version WRT case-sensitivity of the SetUp call. Mind you if you've installed it via SQL Test this shouldn't be a problem.

If these suggestions don't work, let me know and I'll see what else I can come up with

In terms of achieving DRY, you do seem to have a lot of setup here, And that setup seems to be calling production procedures.

Obviously I don't know about the underlying logic but if it were me, I would ask myself whether it really is necessary to call all those, or even any of them.

If I understand this correctly, the procedure under test is apPROMsBoardSummaryReport.

I am presuming that this manipulates data from a number of tables (presumably populated by the previous procedure calls).

If I wanted to isolate this test from everything else, I wopuld endeavour to just populate the tables and columns that are required for this one test.

As a simple example, if I had an [OrderDetail] table with [Price] and [Quantity] columns, and the procedure was supposed to generate a [Cost] column, then at the simplest level these are the only columns that need to be populated to fulfil that test. Only populate the tables columns that are essential to the test, use tSQLt.FakeTable to achieve this where necessary.

If you do use tSQLt.FakeTable, all columns on the mock table are defined as nullable - precisely to allow you to populate only the columns required for the test. Where this really helps is when another NOT NULL column is added to [OrderDetail], your existing tests won't break because they're not populating that column.

Test Driven Development requires a different mindset, look for ways to break functionality down to the lowest common denominator.

If you need three or four tables to contain data in order to test apPROMsBoardSummaryReport, than add rows to those directly. As it stands, if the test you demonstrate here fails, how do you know where the failure is? Looking at your test, it could just as easily be in apSaveUploadedScoreCardDetailsNew, apSaveUploadedScoreCardResults or apAddUploadedScorecardHeader. Those should be subject to their own tests that assert they do everything they should. Otherwise, what you have here is an integration test - which is still useful but much harder to identify the cause of any failure.

There is pattern called Test Data Builder used in the .Net and Java world that can be used to simplify this kind of set up. For example Adapting the Test Data Builder Pattern for T-SQL. Using TDB, you could write a procedure that might take all the inputs from the production procedure calls you are making and puts the correct values in the appropriate tables and columns. Then you have just one procedure call which can be reused in other tests passing different values.

Even if the production procedures you are calling in this test are douing complex calculations, the end result is probably a set of numbers in some rows in some tables - it is those simple numbers you need to set up for this test - not all the intevening logic.

If you're familiar with DRY, you will also know about SRP - Single Responsibility Principle.

Sorry if this reply goes on a bit but this is a complex subject. TDD really does change how you look at code - resulting in more robust, better designed code. We just have to get through that learning curve first

There is a lot of set up code at the moment, I was hoping to refactor that out as in your tutorials, I had a go at fake tables but ran into issues so went with the full verbose approach to get things started.

The PROMsBoardSummaryReport procedure is aggregating data from the tables I'm adding to above. There are constraints on those tables (foreign key etc) so faking the tables and only loading the minimum required data for the test is definitely where I'm aiming for.

Then I was hoping to look into the Test Data Builder to further clean up the setup for these tests, but its small baby steps for me at the moment. Which is all good because once I've stumbled through things I can give a good presentation to my team.

I definitely appreciate the help and depth of the reply, when trying to learn something new all the information comes in handy. It's getting over the gap between hello world and real world that's the big hurdle and having someone who knows what's what help out is invaluable

Then add this at the top of the test procedure IF OBJECT_ID(N'BoardSummary.Actual') > 0
RAISERROR('In Test: [Actual] table exists in BoardSummary schema', 0, 1) WITH NOWAIT;
ELSE
RAISERROR('In Test: [Actual] table not found in BoardSummary schema', 0, 1) WITH NOWAIT;

Also, you were correct in naming the set-up procedure [SetUp]. tSQLt looks for this before running each test. If you'd left it as [test SetUp], it would have got called once because it was treated as just a regular test.

Also, in case it wasn't clear earlier, the reason why I recommend putting the [actual] and [expected] tables in the test schema rather than dbo is because one day you might actually be testing a database that has a table named like that.

\"Your mind is like a parachute, it works best when open\" Frank Zappa
\"Be wary of strong drink. It can make you shoot at tax collectors…and miss\" Robert Heinlein
blog: http://datacentricity.nettwitter: @datacentricity

\"Your mind is like a parachute, it works best when open\" Frank Zappa
\"Be wary of strong drink. It can make you shoot at tax collectors…and miss\" Robert Heinlein
blog: http://datacentricity.nettwitter: @datacentricity

Simon, just a thought - if you're having issues with FakeTable it might be worth starting a new thread.

FakeTable itself is pretty reliable in my experience, but schema-level tasts can introduce some interesting questions around how we do certain types of test.

\"Your mind is like a parachute, it works best when open\" Frank Zappa
\"Be wary of strong drink. It can make you shoot at tax collectors…and miss\" Robert Heinlein
blog: http://datacentricity.nettwitter: @datacentricity

there are other similar failures on all my tests. It looked to me like the method simply wasn't being called. The class I've got for all my tests is BoardSummary, I created a new test in the class then renamed it to SetUp so I'm confident it is in the right class - but either I've massively mucked up the setup method or it isn't being called before the tests... I'm not sure.

You can try quickly throwing a PRINT statement into the SetUp procedure and exceute the test(s) acheck the output in SSMS see if it is definitely being called.

As with other users, specifying the schema correctly in all references is a common typo/gotcha.

Are you running against a case-sensitive instance/database? There were a couple of historic bugs around casing for the Setup procedure name - fixed in different versions.

Otherwise, try posting your code and we'll see if we can help

\"Your mind is like a parachute, it works best when open\" Frank Zappa
\"Be wary of strong drink. It can make you shoot at tax collectors…and miss\" Robert Heinlein
blog: http://datacentricity.nettwitter: @datacentricity