Q: whats the best way to achieve database test driven development in sql server 2008 I am develeping a solution to achieved database test driven develepment for testing stored procedure changes. My thoughts so far is to execute the stored proc and store the expected data and schema results set in a raw format. Make the sp change then execute the stored proc again and assert that the schema and data are equal.
At 1st I stored the data in a sql server database like:-
testdata.storedproc


*

*ID - int

*ResultsSet - int

*Data - XML 

*Date - DateTime


testschema.storedproc


*

*ID - int.

*ResultsSet - int.

*Schema - XML.

*Date - DateTime.


All was going well until i found a stored proc that has is causing me headache:-
The stored procedure :-


*

*has multiple results sets.

*has columns that contains the same column name ie. (person.name, area.name).

*has data contains with illegal xml characters.

*has results in excess of 120000 rows.


This breaks my intital soultion, does anyone have any knowlege on database test driven development that they would like to share, or have alternative solutions that could be of use?

A: I have had a great deal of success using the DbFit library (in conjunction with Fit/Fitnesse) to develop stored procedures in a test-first manner. Tests are written in HTML, either as standalone files (Fit) or Wiki pages (FitNesse). DbFit manages the database connectivity, executes queries and stored procedures, and (by default) wraps each individual test in a transaction to help keep things repeatable.
For more on Fit/Fitnesse, see the FitNesse site and the questions tagged fitnesse here on StackOverflow. DbFit can be found on GitHub.

A: Solutions for test data storage
You are going with a simple raw storage format, which as you've discovered isn't very helpful when your sprocs are free to play around in an entire relational database.
Instead you should leave your result set from each test in a database.  Then compare the data in that DB to another result DB you've set up, or a simple data format that is capable of representing multiple rows and columns of data in multiple tables.
You could author your expected test result data as DML (possibly extracted from an actual DB), a custom Xml format that can handle more complex data schemas, or simply do a DB backup/restore on a custom result set DB.
Personally, I'd try to cheat by using a custom test-only Entity Framework or NHibernate model to import the schema (but none of the sprocs, or anything that might make my testing harder), and use the CreateDatabase feature to deploy the schema for tests.  Then you can take advantage of .Net code to store your reference data (object initializers), the ORM you're using (to extract and compare the data), and NUnit.  This meshes well with the brainstorming I've had below.
Some brainstorming I've had on your higher level problem
Test cases ideally will test logic in isolation.  Since you can't really split up a single stored procedure, you can at least run it in isolation.
Here's a general perscription for the things you would need to implement a stored procedure test.  Note that I speak of these all in terms of the DB, but you could replace some of it with whatever makes your tools and test cases easier to write, understand, and maintain.
Parts
Input data


*

*A minimal stripped-down schema that the sproc references

*

*One part is the minimal DDL that will get the sproc to compile

*The other part is the minimal DML that feeds the sproc for this test case


*Stripped down mock versions of any sproc that the current sproc references

*The data to feed into the sproc's parameters

*The script for the sproc to test


Output data


*

*The expected final data set for every table the sproc touches

*

*This is just a second set of DML run against the earlier schema


*The expected data set returned from the sproc


Tooling


*

*An empty schema to run the test case against

*An empty schema to push the expected results to for comparison

*Tooling or scripts to compare data between tables (your test assertions)


Some of this can be shared between test cases for the same sproc.  You could share the schema DDL, maybe some of the DML, and the logic to set up/execute/compare/clean up for each test.
Execution
Arrange


*

*Create the schema under test (it will start out empty)

*Run the DDL scripts

*Run the initial DML scripts

*Create the mock sprocs

*Create the sproc under test


Act


*

*Execute the sproc under test, and store the result set somewhere (another table? set of tables?)


Assert


*

*Create a second empty schema for your results

*Run the DDL scripts

*Run the expected DML scripts

*Possibly copy over the sproc's return results to a new table in this new schema?

*Run your comparison scripts between the two schemas, and output whether the comparison succeeded (the data was identical) or failed (there was any difference in the data).
For diagnostic purposes, the data should be identical, or a nice error should be output, telling you what the differences are.

