How do I fake / fake RaiseError using StoredProcedure

Question

This is my first day with tsqlt, so you can expect some vague statements.

I am trying to test a stored procedure that has Try Catch Block, but the actual statements in the test are the insert and update command.

Now I want to check if, in the case of an ErrorRaised error, my catch block performs the expected tasks.

Can you please direct me to the question of how I can raise an error from a stored procedure in a test, where we have nothing to mock / fake inside.

I hope my question is clear, we will clarify with pleasure, if necessary.

Answer 1

, , , catch?

, catch. :

create table mySimpleTable
(
  Id int not null primary key
, StringVar varchar(8) null 
, IntVar tinyint null
)
go

, .

, . (-), , . , , . catch Log4TSql, SourceForge.

, , - , catch, , . raiserror ( throw SQL2012) catch. , , , (.. ).

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

() .

exec tSQLt.NewTestClass 'mySimpleTableInsertTests' ;
go

- , catch, . exec tSQLt.ExpectException , , @Id NULL ( )

create procedure [mySimpleTableInsertTests].[test throws error from catch block]
as
begin
    exec tSQLt.ExpectException @ExpectedErrorNumber = 50000;

    --! Act
    exec dbo.mySimpleTableInsert @Id = null
end;
go

tsqlt.SpyProcedure "" ExceptionHandler, . , , tSQLt , , , spied , . . , ExceptionHandler . , ExceptionHander mySimpleTableInsert .

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

, ( ) , , , @IntVar :

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

, , , .

Answer 2

RAISERROR SQL Server :

RAISERROR ('Error raised in TRY block.', -- Message text.
               16, -- Severity.
               1 -- State.
               );

- MSDN: RAISERROR