DRY tests

May 15, 2009 at 5:46 PM

Thank you for this tool. It is pretty cool.

As I was creating my tests, I wanted to keep them DRY as possible. When creating  #ActualResult or #ExpectedResult that are the same, I came up with an interesting way to keep the code short and not repeating much.


table Example ( example_id int , example_name varchar(100) )


I want to compare the table results from a stored procedure several times in different tests. I created a helper procedure to keep this simple. This example is simple. I am using this with a return set with 26 columns, so I didn't want to keep typing them out. This allows me to type it one time and add the columns when they are needed.  There could be a performance hit but I havent encountered any significant loss yet.


create proc ExampleReturn @tmpTable varchar(100) as


declare @sql varchar(max)

set @sql = 'alter table ' + @tmpTable + ' add

example_id int

, example_name varchar(100)


exec sp_executesql @sql



How to Use inside the test:

create table #ActualResult ( id int identity primary key )

exec ExampleReturn '#ActualResult' --adds the extra columns to the temp table so you dont have to keep typing them.



Thanks again for this tool!!! I have been wanting something like this for a while. I think you have put this together very well.

May 18, 2009 at 9:15 PM
Edited May 18, 2009 at 9:21 PM

Thank you jhamm for your nice words.

Very good idea separating the create table statement from the part where the columns are added. Indeed, because of the way temp tables are scoped in T-SQL you cannot put the create part in a setup sproc. I can see cases where a large number of tests will have to deal with the same tables over and over and your method will simplify things nicely.