RegisterExpectedError masking subsequent "EXEC TST.Assert.TableEquals" failure

Oct 11, 2011 at 2:04 AM


Using RegisterExpectedError and then calling the sp that always results in the expected error is masking subsequent "EXEC TST.Assert.TableEquals"

I have changed the sp that I'm testing so the ExpectedResult & ActualResult will be different and therefore I expect the test to fail.
As above, the test isn't failing and the errors are getting masked.

Hopefully that makes sense.

Sample sql below:


-- test NULL batchnum
EXEC TST.Assert.RegisterExpectedError @ContextMessage            = 'Testing NULL batch number'
                                     ,@ExpectedErrorMessage      = 'The batch number is NULL. It needs to be passed into the SP.'
                                     ,@ExpectedErrorProcedure    = 'usp_FIN_blahblahblah'

EXEC dbo.usp_FIN_blahblahblah @BatchNum, @StartDate, @EndDate

exec dbo.usp_FIN_blahblahblah_BatchNumber @BatchNum output

EXEC dbo.usp_FIN_blahblahblah @BatchNum, @StartDate, @EndDate

insert into #ActualResult
from bbo.FIN_blahblahblah where trxdate = @TrxDate -- #ExpectedResult and #ActualResult. EXEC TST.Assert.TableEquals 'Comparing two tables for FIN_blahblahblah creation' EXEC TST.Assert.IsTableNotEmpty 'Comparing two tables for FIN_blahblahblah - not empty'

Oct 14, 2011 at 2:41 AM

When the error that you expect occurs, it will interrupt the execution of the test stored procedure (think a throw in C#). The rest of the test stored procedure – this includes the part that has the Assert.TableEquals will not have a chance to run.

If you really need to do this type of test (although it looks like a bad design – see the next paragraph) you should get rid of RegisterExpectedError and wrap the call that is supposed to fail in a TRY/CATCH. In the CATCH block you can use functions like ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_NUMBER() and so on to retrieve the information you need to validate that the error that occurred was the expected one. Use Assert.Equals or some Assert to do the actual validation. Then, after the CATCH block, continue with your test procedure and eventually with TableEquals and IsTableNotEmpty. Basically you implement on your own the functionality of RegisterExpectedError. This will prevent the execution of your test stored procedure to be interrupted at the point where the error occurs.

Your scenario may indicate a bad design. Do you really need to validate the results of a stored procedure after it encounters an error? This is akin to saying that you have a system where a caller should still consume the results of a stored procedure even in the situation where that stored procedure fails. I would look again and try to rethink the design for this scenario.


Oct 14, 2011 at 4:47 AM

Thanks, you have provided a clear answer.

I just have basic parameter validation in my SP as follows:

	if @BatchNum is null
		RAISERROR ('The batch number is NULL. It needs to be passed into the SP.',
As with all tests, I wanted to verify that this (error) condition was working. From reading the documentation, RegisterExpectedError seemed to be the only way that the framework can test error messages. It looks like RegisterExpectedError is just used so the framework knows about certain errors. I'll rewrite my test to use TRY/CATCH instead.