I wrote a short piece the other day trying to show how one can use tSQLt to test code. It’s a simple test built against a user defined function. It works well and when the test is run, it passes.
Here was my code:
ALTER function [dbo].[calculateEstimateOfReadingTime] ( @value varchar(max) ) returns int as begin declare @ret as int = 1 , @i as int = 1; while @i <= len(@value) begin if substring(@value, @i, 1) = ' ' begin set @ret = @ret + 1; end set @i = @i + 1; end return @ret / 250; ; end
Someone in the comments pointed out that we can more efficiently refactor this code to :
ALTER FUNCTION [dbo].[calculateEstimateOfReadingTime] ( @value varchar(max) ) RETURNS int AS BEGIN RETURN ( SELECT LEN(@value) - LEN(REPLACE(RTRIM(@value), ' ', '')) + 1 ) END
However when I run the test, I get these results:
That’s not good, but that’s why we test.
I could easily see someone refactoring the code, finding a more elegant method of rewriting this code and after running some quick tests, they check this in to source control (hopefully) and maybe deploy it to production. Hopefully QA catches this, but wouldn’t we want to notice this in development?
The refactored code misses a divide by 250.
Write tests, use them to catch mistakes. These simple ones slip through at times and are what make deployments really, really stressful.