So if I understand your question correctly, you are trying to test that your catch block works?
The way to do this will depend on what happens within your catch block. Imagine this scenario:
create table mySimpleTable
(
Id int not null primary key
, StringVar varchar(8) null
, IntVar tinyint null
)
go
We have a stored procedure that inserts data into this table thus.
This is based on a template that I use in many of my procedures. It starts by validating the input(s), then does the work it needs to do. Naming each step can be particularly useful to understand where the error occurred in more complex, multi-step procedures. The catch block uses my Log4TSql logging framework that you can read more about on my blog and download from SourceForge.
The pattern I follow is to capture information about the exception and what the procedure was doing at the time of the error within the catch block but ensure that an error is still thrown at the end of the procedure. You could also choose to call raiserror (also throw on SQL2012) within the catch block. Either way, I believe that if a procedure hits an exception it should always be notified up the chain (i.e. never hidden).
create procedure mySimpleTableInsert
(
@Id int
, @StringVar varchar(16) = null
, @IntVar int = null
)
as
begin
--! Standard/ExceptionHandler variables
declare @_FunctionName nvarchar(255) = quotename(object_schema_name(@@procid))
+ '.' + quotename(object_name(@@procid));
declare @_Error int = 0;
declare @_ReturnValue int;
declare @_RowCount int = 0;
declare @_Step varchar(128);
declare @_Message nvarchar(1000);
declare @_ErrorContext nvarchar(512);
begin try
set @_Step = 'Validate Inputs'
if @Id is null raiserror('@Id is invalid: %i', 16, 1, @Id);
set @_Step = 'Add Row'
insert dbo.mySimpleTable (Id, StringVar, IntVar)
values (@Id, @StringVar, @IntVar)
end try
begin catch
set @_ErrorContext = 'Failed to add row to mySimpleTable at step: '
+ coalesce('[' + @_Step + ']', 'NULL')
exec log4.ExceptionHandler
@ErrorContext = @_ErrorContext
, @ErrorProcedure = @_FunctionName
, @ErrorNumber = @_Error out
, @ReturnMessage = @_Message out
;
end catch
--! Finally, throw any exception that will be detected by the caller
if @_Error > 0 raiserror(@_Message, 16, 99);
set nocount off;
--! Return the value of @@ERROR (which will be zero on success)
return (@_Error);
end
go
Let's start by creating a new schema (class) to hold our tests.
exec tSQLt.NewTestClass 'mySimpleTableInsertTests' ;
go
Our first test is the simplest and just checks that even if an exception is caught by our catch block, an error is still returned by the procedure. In this test we simply use exec tSQLt.ExpectException to check that an error is raised when @Id is supplied as NULL (which fails our input validation checks)
create procedure [mySimpleTableInsertTests].[test throws error from catch block]
as
begin
exec tSQLt.ExpectException @ExpectedErrorNumber = 50000;
--! Act
exec dbo.mySimpleTableInsert @Id = null
end;
go
Our second test is a little more complex and makes use of tsqlt.SpyProcedure to "mock" the ExceptionHandler that would otherwise record the exception. Under the hood, when we mock a procedure in this way, tSQLt creates a table named after the procedure being spied and replaces the spied procedure with one that just writes the input parameter values to that table. This is all rolled back at the end of the test. This allows us to can check that ExceptionHandler was called and what values were passed into it. In this test we check that ExceptionHander was called by mySimpleTableInsert as a result of an input validation error.
create procedure [mySimpleTableInsertTests].[test calls ExceptionHandler on error]
as
begin
--! Set the Error returned by ExceptionHandler to zero so the sproc under test doesn't throw the error
exec tsqlt.SpyProcedure 'log4.ExceptionHandler', 'set @ErrorNumber = 0;';
select
cast('Failed to add row to mySimpleTable at step: [Validate inputs]' as varchar(max)) as [ErrorContext]
, '[dbo].[mySimpleTableInsert]' as [ErrorProcedure]
into
#expected
--! Act
exec dbo.mySimpleTableInsert @Id = null
--! Assert
select
ErrorContext
, ErrorProcedure
into
#actual
from
log4.ExceptionHandler_SpyProcedureLog;
--! Assert
exec tSQLt.AssertEqualsTable '#expected', '#actual';
end;
go
Finally the following (somewhat contrived) examples use the same pattern to check that an error is caught and thrown if the value of @IntVar is too big for the table:
create procedure [mySimpleTableInsertTests].[test calls ExceptionHandler on invalid IntVar input]
as
begin
--! Set the Error returned by ExceptionHandler to zero so the sproc under test doesn't throw the error
exec tsqlt.SpyProcedure 'log4.ExceptionHandler', 'set @ErrorNumber = 0;';
select
cast('Failed to add row to mySimpleTable at step: [Add Row]' as varchar(max)) as [ErrorContext]
, '[dbo].[mySimpleTableInsert]' as [ErrorProcedure]
into
#expected
--! Act
exec dbo.mySimpleTableInsert @Id = 1, @IntVar = 500
--! Assert
select
ErrorContext
, ErrorProcedure
into
#actual
from
log4.ExceptionHandler_SpyProcedureLog;
--! Assert
exec tSQLt.AssertEqualsTable '#expected', '#actual';
end;
go
create procedure [mySimpleTableInsertTests].[test throws error on invalid IntVar input]
as
begin
exec tSQLt.ExpectException @ExpectedErrorNumber = 50000;
--! Act
exec dbo.mySimpleTableInsert @Id = 1, @IntVar = 500
end;
go
If this doesn't answer your question, perhaps you could post an example of what you are trying to achieve.
