none
RAISERROR with Try/Catch does not exit after exception in catch block

    Întrebare

  •  

    I am trying to propogate an error from within my proc out to the caller.

    In the attached example I have 2 sets of try catch blocks.

    • I raiserror in the first
    • catch the error and then raiserror again. (I expect to exit)

    I do not expect :

    • to get to print 'post test'
    • to get to second try block.

    but this does not exit, instead the code flows as per 2 runs.

    I do not understand the reason for the flows, as it seems counterintuitive to be raising an error but then still print following exceptions. I cannot seem to find any references that explains this behaviour.

     running tests together results

    print '-------------------------------------------------------' print 'test 15' exec test_raiseerror 15 print '-------------------------------------------------------' print 'test 16' exec test_raiseerror 16 print '-------------------------------------------------------' print 'test 17' exec test_raiseerror 17 print '-------------------------------------------------------' print 'test 18' exec test_raiseerror 18 print '-------------------------------------------------------' 'RESULTS' ------------------------------------------------------- test 15 error number provided: 15 Msg 50000, Level 15, State 1, Procedure test_raiseerror, Line 21 name hello 15 post test 15 Msg 50000, Level 15, State 1, Procedure test_raiseerror, Line 37 name hello 2 15 post test2 ------------------------------------------------------- test 16 error number provided: 16 Msg 50000, Level 16, State 1, Procedure test_raiseerror, Line 21 name hello 16 post test 16 Msg 50000, Level 16, State 1, Procedure test_raiseerror, Line 37 name hello 2 16 post test2 ------------------------------------------------------- test 17 error number provided: 17 post test 17 post test2 ------------------------------------------------------- test 18 error number provided: 18 post test 18 post test2 ------------------------------------------------------- Msg 50000, Level 17, State 1, Procedure test_raiseerror, Line 21 name hello 17 Msg 50000, Level 17, State 1, Procedure test_raiseerror, Line 37 name hello 2 17 Msg 50000, Level 18, State 1, Procedure test_raiseerror, Line 21 name hello 18 Msg 50000, Level 18, State 1, Procedure test_raiseerror, Line 37 name hello 2 18

    run tests seperately

    exec test_raiseerror 15 error number provided: 15 RESULTS 15 Msg 50000, Level 15, State 1, Procedure test_raiseerror, Line 21 name hello 15 post test 15 Msg 50000, Level 15, State 1, Procedure test_raiseerror, Line 37 name hello 2 15 post test2 exec test_raiseerror 16 RESULTS 16 error number provided: 16 Msg 50000, Level 16, State 1, Procedure test_raiseerror, Line 21 name hello 16 post test 16 Msg 50000, Level 16, State 1, Procedure test_raiseerror, Line 37 name hello 2 16 post test2 exec test_raiseerror 17 RESULTS 17 error number provided: 17 post test 17 post test2 Msg 50000, Level 17, State 1, Procedure test_raiseerror, Line 21 name hello 17 Msg 50000, Level 17, State 1, Procedure test_raiseerror, Line 37 name hello 2 17 exec test_raiseerror 18 RESULTS 18 error number provided: 18 post test 18 post test2 Msg 50000, Level 18, State 1, Procedure test_raiseerror, Line 21 name hello 18 Msg 50000, Level 18, State 1, Procedure test_raiseerror, Line 37 name hello 2 18



     CODEBLOCK:

    if object_id('test_raiseerror','P') is not null
    	drop proc test_raiseerror
    go
    create proc test_raiseerror(@id as int) as
    begin
    begin try
    	declare @name varchar(20)
    	select @name = 'hello'
    	raiserror('name %s %d',@id,1,@name,@id)
    	print 'next'
    end try 
    begin catch
    	
    	declare @errormessage nvarchar(4000)
    	declare @errornum int
    	select @errormessage = error_message()
    	,      @errornum = error_severity()
    	
    	print 'error number provided: ' + convert(varchar(2),@errornum)
    	raiserror(@errormessage, @errornum,1)
    	print 'post test'
    end catch
    begin try
    	select @name = 'hello 2'
    	
    	raiserror('name %s %d', @id,1,@name, @id)
    end try
    begin catch
    	select @errormessage = error_message()
    	,      @errornum = error_severity()
    	
    	print @errornum
    	raiserror(@errormessage, @errornum,1)
    	print 'post test2'
    end catch
    end
    go

    sqlserver 2008 & 2008 R2
    • Editat de incre-d 15 martie 2012 18:28 add sqlserver version
    15 martie 2012 18:05

Răspunsuri

  • Further to this.

    Through experimentation I have found that when I wrap the 2 try catch blocks within an outer catch block. The code behaves as expected.

    • Marcat ca răspuns de incre-d 16 martie 2012 09:15
    16 martie 2012 09:14

Toate mesajele

  • There is a Connect that describes a similiar complaint.  But basically a raiserror inside a catch block does not terminate the procedure, it will continue with any additional code in the CATCH and FINALLY unless it hits a return statement.

    http://connect.microsoft.com/SQLServer/feedback/details/275308/have-raiserror-work-with-xact-abort

    • Propus ca răspuns de Naomi NModerator 15 martie 2012 23:55
    • Marcat ca răspuns de incre-d 16 martie 2012 09:07
    • Anulare marcare ca răspuns de incre-d 16 martie 2012 09:12
    • Anulare propunere ca răspuns de incre-d 16 martie 2012 09:15
    15 martie 2012 19:33
  • Further to this.

    Through experimentation I have found that when I wrap the 2 try catch blocks within an outer catch block. The code behaves as expected.

    • Marcat ca răspuns de incre-d 16 martie 2012 09:15
    16 martie 2012 09:14
  •    I have noticed this as you describe but only in the outermost catch block, in you have nested blocks (even procedures calling other procedures) then raiserror stops and execution continues in the outer block, check details here:

    https://social.technet.microsoft.com/Forums/en-US/eba1b677-123a-470a-95f2-f11bf4cb3b90/raiserror-behavior-in-trycatch-block?forum=transactsql

    în urmă cu 14 ore şi 7 minute