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

• 15 martie 2012 18:05

   

   
  

  0
   

  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
  •  
  • Mesaj
  • Citare