Error Handling

• thehammer-178982

  SSC Eights!

  Points: 907

  More actions

  January 16, 2006 at 11:14 am

  #92845

  I am running the following code which I expect to fail and I can't understand why the error handling is not working

  declare @result int 

  EXEC @result = sp_password 'old_password', 'new_password, 'user'

     if @@error = 15211

      print 'failure'

  It is not printing the error message "failure"! I have also converted @@error with the following code and it displays 0 i.e. zero!!

  declare @errornum  int

  SELECT @errornum=@@error

     PRINT 'ERRORNUM = ' + CONVERT(CHAR(5),@errornum)

  Can anyone please shed any light?

  This is what query analyser returns

  Server: Msg 15211, Level 16, State 1, Procedure sp_password, Line 58

  Old (current) password incorrect for user. The password was not changed.

   

• PW-201837

  SSC-Insane

  Points: 20805

  More actions

  January 16, 2006 at 11:24 am

  #614972

  If an error has a high enough severity, you can't trap it because the entire batch is terminated and rolled back at that point. Your "If @@Error .." statement never gets to execute because of the level 16 error.

   

• Sergiy

  SSC Guru

  Points: 110192

  More actions

  January 16, 2006 at 2:30 pm

  #614999

  You are assignig error code returning by SP to @Result and than check @@error.

  What would you expect?

  @@error reports you if "EXEC" was failed, e.g. no such SP, wrong number of parameters, etc.

  But if you want to get result of SP execution check @result:

  EXEC @result = sp_password 'old_password', 'new_password, 'user'

     if @result  = 15211

      print 'failure'

  _____________
  Code for TallyGenerator