Test Driven Development with SQL Server Databases Example

In a previous post I suggested a method of using Test Driven Development with SQL Server stored procedures. Someone suggested some examples would be helpful.

Example Scenario

You have a database with a customers table. This table (TDDCUSTOMERS) contains the fields CUSTOMERID and NAME.

You write a stored procedure to add a customer. You are worried that someone might want to add more fields to the TDDCUSTOMERS table, and this will break the stored procedure.

Setting up

Create the table as follows:

use northwind
go
create table dbo.TDDCUSTOMERS 
(
 CUSTOMERID int identity(1,1),
 CUSTOMERNAME varchar(50)
)
go

Write your test

Then think about how you are going to test your Stored Procedure to add a customer - a simple test is to check if there is one more row in your database if a customer has been added and write your test:

use northwind
go
create procedure dbo.TDDTESTADDCUSTOMER
 (@c integer output)
as
begin transaction
declare @c1 as integer
declare @c2 as integer

select @c1=count(*) from dbo.tddcustomers 
exec dbo.TDDADDCUSTOMER 'Richard'
select @c2=count(*)  from dbo.tddcustomers

if ((@c1 + 1) = @c2)
begin

 rollback transaction
 set @c = 1
end
else
begin

 rollback transaction
 set @c = 0
end

Make your test fail

Then write a stored procedure to do nothing:

use northwind
go
create procedure dbo.TDDADDCUSTOMER(@name 
varchar(50))
as
print ''

Run your test

declare @c as integer
exec TDDTESTADDCUSTOMER @c output
print @c

This should output 0 as the SP has failed.

Make your test work

You will then have to make your test work:

alter procedure dbo.TDDADDCUSTOMER(@name 
varchar(50))
as
insert into dbo.tddcustomers(customername) 
values(@name)

Now run the test again

declare @c as integer
exec TDDTESTADDCUSTOMER @c output
print @c

Note this returns 1. If you type select * from TDDCUSTOMERS this will not return any records, as the transaction was rolled back, so your database will not be corrupted.

Make a change to the database schema

Now try changing your schema to add an address field (with a not null constraint) to the TDDCUSTOMERS table

drop table tddcustomers
go
create table dbo.TDDCUSTOMERS 
(
 CUSTOMERID int identity(1,1),
 CUSTOMERNAME varchar(50),
 ADDRESS varchar(50) not null
)
go

Run the test again

declare @c as integer
exec TDDTESTADDCUSTOMER @c output
print @c

This will return 0, as a customer cannot be added using the stored procedure (as the address field cannot be NULL and there is no default). Your test is easy to run, and can be run often, so you will detect that adding an ADDRESS field will break the stored procedure. You then know to rewrite the stored procedure to put in a default address, so your application continues to work.

Technorati Tags: SQL, TDD, Database