OK, I now have a C# assembly that has a method called tableCompare that takes a stored Proc name and a table name as parameters and it then connects to the database and gets gets the structure of the table and calls the stored proc and captures the structure of the return ...

Current they just print out to the SQL message window, next is to start the comparison system ... I am stoked at this ... and the techniques are not that hard to do once you get used to it.

OK, the further I go with this the more needs doing (is that not always the way).

So here is where we got this morning. I was replacing all the If @@ERROR <> 0 with TRY/CATCH when I noticed some weird behavior.

The Suite setup and tear down were being called before each test in the suite, not at the start and end of the suite (respectively) and that the procedure that built the list of tests to be executed explicitely checked for the _Setup and _teardown on the suites.

Well that struck me as dumb ... so change the Cursor Select statement to exclude stored procs in the unitTest Schema if they end in '_Setup' or '_TearDown' ... and then just treat all the rest of the stored procs as tests to be executed ... much easier ...

Then on to the procedure that actually executes the tests in a suite ...

Before starting on the tests in now does the following ... (the name of the Suite is passed to it when it is called as @suite)

if there is a stored proc called @suite_Setup run it.
For each test in the suite
- If there is a stored proc called @testName_Setup run it
- Run the test
- If there is a stored proc called @testName_TearDown run it
Next Test
If there is a stored proc call @suite_TearDown run it

Much better logic and removes lots of strig checks and stuff from the generation of the list of tests.

It was also coded to take into account a couple of issues with the way errors were handled in SQL 2000 (or in some cases not handled). So there was lots of rollbacks and other stuff and tables to store stuff if stuff broke because the error trapping might not work. Well error trapping is now lots better and most of this is no longer needed ... so I have begun to week this out. It is a big job as it is embedded all through the structure ... but I am getting there.

One of the things with the tsqlUnit code is that it uses inline error trapping not the try/catch functionality that was brought in from SQL Server 2005.

Today's project is to attack the major stored procs and make use of the Try/Catch syntax to better handle errors rather than the inline error techniques. This should improve the stability and general performance of the system.

I will then look at the .Net stored proc system for the structure comparisons.

OK. So my development of the tsqlUnit has hit a snag ... namely I can not persist variables form test to test without a work around.

So ... off to the workaround shop and I have added a new table to tsqlUnit called tsu.variableStore that has 2 nvarchar field one for the name and one for the value ... so that you can store stuff in here. You have to cast the variable from what ever datatype it is into a nvarchar and then store it and once retreived cast it back to the required datatype.

To stop proliferation into this table it is explicitly emptied at the end of each test suites execution. This means that the test author only has to worry about storing the data and retrieving/using it. The framework deletes it for them, makeing this implicit tear down for the user (while explicit for the framework).

Next is to set up a mcok schema with the same sort of rules so that players can mock tables adn stored proc's into this schema and the framework will remove them after test execution.

Nifty huh.

On other news I found out that TSQL will not allow me to insect the meta data on a recordset returned by a stored proc of a talbe type function. I could define a temp table and select into that, but it will implicitely cast datatyes to massage the data were it can and only throw and error if it can't ... which does not help use in testing the resulting output from the stored proc.

What I am now investigating is using a .Net stored proc to do this. We have enabled the CLR in our SQL Server instance so I can write a function that takes a stored proc name and some variables (if needed) and then compare the returned recordset structure with a reference structure (still deciding on how to store this). It seems a bit of weird way to do it, but if it works I will not complain.

So while I was working on a project at CCP I needed to do some checking of data and stored procedures in a MS SQL 2005 database and rather than write my own I went a looking for an existing framework. I found tsqlUnit on SourceForge. It had not been updated or worked on in sometime byt it had the core oundation that I needed. But I had to do some work to get it going.

So off to work I went.

• Completed Work
• make it case sensitive - Done.
• move all the framework tables and stored procs into a seperate schema (the 'tsu' schema) - Done
• change all the 'message' fields in the framework to nvarchar(max) from nvarchar(255) - Done
• move all unit tests into a seperate schema and removed the prefix requirements- Done
• update the license and copyright information - Done
• update the main stored proc to make it's output parameters compatible with our continuous build environment - Done

Todo
Add functions and procedures to validate table format (so I can do an example format and then test that a procedure/function is returnign data in that format.

Once this final bit of work is done I will resubmit it back into the Open Source community and see what they think.

Now some reasonings for the work.

• Case sensitive ... well simple really our db's are case sensitive.
• Move framework into it's own schema ... makes it easier to seperate from the rest of the database and also user security is easier.
• Message size ... we had some very detailed error messages that we wanted to return and 255 characters was not enough.
• Unit test schema ... by having all the stored procs in another schema made the location of these and the sequential running of them much easier. Also it was much easier to remove them from general veiwing by making the schema not visible to standard users.
• License ... as the old code had not been changed in sometime and my changes had effected all tables and stored procs (most tables were just name changes)  had been modified, referenced the original author showing that this was a derivative work.
• Main runner change ... we have an application that controls our continuous build process and all our unit testing frameworks have had wrappers to make them report back to this application in a standard manner. Means that we get all our result information in a single place.

All in all I am rather happy with this bit of work. It has been a fun learning experience and I learnt a couple of things about tsql I did not know.