(Using Visual Studio Team Suite edition for Database Professionals. A.k.a. “data-dude” because someone at Microsoft forgot to drink their creative juice that morning.)
Although I enjoy the unit-testing features of Visual Studio "Database Professional" edition (or whatever they're calling it these days), it is a ghastly oversight that you can't easily test for a procedure producing a specific expected error result.
Instead, if the database code itself experiences an error, the whole test blows chunks—that is, it fails. No matter if it got exactly the error you expected it to. I concede it's (all too) common to have little/poor/no error handling in stored procedures—but come on Microsoft! Let's encourage better practices.
I would expect to have one of the "Test Conditions" be for checking that a specific error code was produced. This would be about the 2nd most useful one (after checking for a Scalar value, which handles just about everything anyway).
(Yeah, I know, it's "extensible" and I'm free to spend a bunch of time developing my own Test Condition and plugging in it and sharing that code with everyone on the planet. But seriously? Who buys this latest MS excuse for not thinking about how to build what the community needs in the first place?)
<sigh>
So an aha! moment hit the other night which led me to this simple workaround. Let's use the trusty Scalar Value test condition combined with a TRY/CATCH block within the SQL code to test itself. Like this:
begin try
exec @rc = dbo.SomeProcedure @Param='some value';
select @@error as ErrorNbr;
end try
begin catch
select @@error as ErrorNbr;
end catch
Then, we just wire up a Scalar Value to test the 1st column / 1st row for the specific Error Number you expect this to generate.
(Don't get me started on using ordinal row/column numbers for this—wow, MS has completely lost sight of what relational DBs are supposed to be!)
On my current project, we're returning a specifically formatted XML string from database procedure errors, which include a number of fixed attributes we then parse on the client. Works great. But it has a side effect of all custom-raised errors being #50000. So in this case I'm using a slightly more complex way of testing for expected errors in the CATCH part:
begin try
exec @rc = dbo.SomeProcedure @Param='some value';
select @@error as ErrorNbr;
end try
begin catch
declare @errmsg xml; set @errmsg = cast(error_message() as xml);
select @errmsg.value('(//@ErrorNbr)[1]', 'int') as ErrorNbr
end catch
Happy error hunting!
413c42dd-777a-479e-8774-50c4c5f3de6c|0|.0
Software Engineering, MS SQL Server
devstudio, tsql