Invalid error number returned by system SPs

• Sonal Jain

  Valued Member

  Points: 57

  More actions

  March 11, 2004 at 10:50 pm

  #84248

  if i try to execute a system SP like 'sp_addmessage' then the value of @@error is 0 instead of the actual error number which should be 15009.

  for other scenarios in case of errors i get the actual error number in @@error.

  similiarly for other sp_ Stored Procs if an error is returned then i am not able to get its error number.

  However when i give the command raiseerror(15009,16,1) and then if i check the value of @@error in this case i get the error number.

   

   

  please help.....

   

• wz700

  SSCertifiable

  Points: 7133

  More actions

  March 12, 2004 at 4:50 am

  #498471

  system stored procedures use return code value for success or failure. @@error is erased with return statement.

  Sample:

  use pubs

  go

  create proc sperrtest as

   raiserror(15043,16,1)

   select @@error

   return (0)

  go

  declare @returncode int

  exec @returncode=sperrtest

  select @returncode, @@error

  go

  drop proc sperrtest