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