THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

Experiences from writing sp_CascadingDataViewer – DB unit testing and code distribution

Its now been a month since I introduced sp_CascadingDataViewer and I wanted to take the opportunity to talk about a couple of my experiences while writing it.


SQL Server needs packages

Version 1 of sp_CascadingDataViewer is an 899 line stored procedure (view the code at changeset 58999), that’s a lot of code and much of it is repeated. I would have loved to have hived off different parts into dedicated functions but I couldn’t do that because my aim was to keep everything in a single stored procedure thus making it as easy as possible for someone to add it to their SQL Server instance. That is an unfortunate trade-off that we have to make with SQL Server.

Oracle has a solution to this problem called packages. A package is ostensibly a collection of stored procedures and functions that can be distributed and installed as a single unit. That makes them extremely portable and would be an ideal mechanism for me to distribute a collection of stored procedures and functions that make up Cascading Data Viewer.

SQL Server needs something equivalent to Oracle packages.


You need to be doing automated database unit testing

sp_CascadingDataViewer wouldn’t exist without automated database unit testing in Visual Studio. Period.

With something as complicated as sp_CascadingDataViewer there were many many combinations of code, schema and data that could cause is to break. I had to deal with all those combinations and have a way of ensuring that any changes that I made did not break something else and automated testing was the means with which I did that.

I wrote the code for sp_CascadingDataViewer in Visual Studio rather than SQL Server Management Studio (as I do for all my T-SQL code these days) which meant that my code and the tests lived in the same solution. At the time of writing that solution includes 30 tests and all are available in the source code on Codeplex:


With a simple key chord (CTRL+R, CTRL+A) I could deploy my code and run all my tests against it – that’s an incredibly powerful mechanism and I actually find it to be very productive method of development even if you have many tests that need to be run.

Writing sp_CascadingDataViewer switched me on to the value of automated database unit testing and I now advocate its use wherever I go.


Published Saturday, September 18, 2010 6:41 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Anonymous said:

Hi Jamie,

There is a way you can make this work, but most people don't like it: create global stored procedures in the body of your main stored procedure, and call them. This somehow "feels" a bit dirty but in practice it can work quite well if you're trying to reuse bits of code inside of a bigger procedure. The only issue is that because they're stored procs you'll have to work with scalars or pass data around via temp tables; it would be great if we could create temporary UDFs to work around those sticking points.

September 18, 2010 2:44 PM

SSIS Junkie said:

Just a short note here to inform anyone that is interested that I have just made available v1.1 of sp_CascadingDataViewer

September 19, 2010 4:15 PM

SSIS Junkie said:

Over the past eighteen months I have worked on four separate projects for customers that wanted to make

January 2, 2012 11:07 AM

Leave a Comment


This Blog


Privacy Statement