So, what is Unit Testing? According to Wikipedia (http://en.wikipedia.org/wiki/Unit_testing)
In computer programming, unit testing is a method by which individual units of source code, sets of one or more computer program modules together with associated control data, usage procedures, and operating procedures, are tested to determine if they are fit for use. Intuitively, one can view a unit as the smallest testable part of an application. In procedural programming a unit could be an entire module but is more commonly an individual function or procedure. In object-oriented programming a unit is often an entire interface, such as a class, but could be an individual method. Unit tests are created by programmers or occasionally by white box testers during the development process.
Quite a mouthful there. In my experience, a unit test is a small piece of isolated code that gets tested to make sure that it meets the requirements specified for it. For example, let’s say you have a function that multiplies a number by two. Given an input of 12, you expect an output of 24.
f(x) = 2x ---> f(12) = 24
So a unit test would execute the function, and compare the results to what would be expected. If the result is 24, the test succeeds, if it is not, the test fails.
For a basic example like this, the concept of a unit test might seem like overkill, and it probably is. Our function has no dependencies, it is ridiculously simple, etc. Let’s look at another example where things are a little more complex.
Lets create a simple table and stored procedure. Here is the table:
CREATE TABLE [dbo].[Students](
[StudentID] [int] IDENTITY(1,1) NOT NULL,
[StudentFN] [varchar](50) NOT NULL,
[StudentLN] [varchar](50) NOT NULL,
[StudentDOB] [date] NOT NULL,
[studentEmail] [varchar](100) NULL,
[StudentCell] [char](10) NULL,
CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED ( [StudentID] ASC ) )
ON [PRIMARY]
GO
INSERT INTO [dbo].[Students]
([StudentFN], [StudentLN], [StudentDOB], [studentEmail], [StudentCell])
VALUES
('Tim','Harkin','1970-11-12','[email protected]',NULL),
('John','Doe','1970-05-07','[email protected]','1234567890'),
('Ryan','Callahan','1980-08-09','[email protected]',NULL),
('Bruce','Springsteen','1958-03-25','[email protected]',NULL),
('Jack','White','1958-03-25','[email protected]',NULL),
('Grace','Potter','1985-12-08','[email protected]',NULL),
('Florence','Welch','1985-05-30','[email protected]',NULL)
And here is a simple stored procedure to update a user’s email address:
create procedure usp_UpdateEmail (@p_intStudentID int, @p_strNewEmailAddress varchar(100))
AS
BEGIN
UPDATE students
set studentEmail = @p_strNewEmailAddress where StudentID=@p_intStudentID
END
All well and good. You can even test it to see how it works.
exec usp_UpdateEmail 3,'[email protected]'
select * from Students
So this all gets deployed to prod, and that dinky little SELECT * is all of the testing that will ever happen. Ever. You and your colleagues move on to some other project. Then, a week, or a year, or several years down the road, something changes. Maybe you have the columns renamed because the new naming standards, so the column named StudentEmail gets renamed email.
EXEC sp_rename 'dbo.Students.studentEmail', 'Email', 'COLUMN';
Now, you do get this nice little warning….
Caution: Changing any part of an object name could break scripts and stored procedures.
But it has been a few years. There is a procedure to update everything, a procedure to update the last name for a marriage, etc. You updated those, because that was obvious. So the column name goes into prod, and all is well and good, until Jack White decides that he wants to use his [email protected] email address.
So we run the procedure, and….
exec usp_UpdateEmail 4,'[email protected]'
Msg 207, Level 16, State 1, Procedure usp_UpdateEmail, Line 7 Invalid column name 'studentEmail'.
We have a bug.
Could this have been avoided? Absolutely. And that is going to be the point here.