Catch Error When Calling a A Stored Procedure from another Procedure

• Abhinav Kheterpal

  Old Hand

  Points: 384

  More actions

  August 26, 2004 at 5:46 pm

  #360846

  Hi,

  I need some help with Error handling in Stored Procedures

  I am calling a Stored Procedure (eg. SP_A) from another Stored Procedure (eg SP_B). I have the following code in SP B

  BEGIN TRANSACTION

  EXEC SP_A

  IF @@ERROR <> 0

   BEGIN

    ROLLBACK TRANSACTION

   END

   ELSE

   BEGIN

    COMMIT TRANSACTION

   END

  I was hoping, that I will be able to get around having to put Error Check statements after each line of code. But, its not working. Even though there are errors being raised in SP_A. Its not being caught in the check immediately after executing the procedure. Any ideas??

  Is there any other way in SQL Server 2000 to get around the problem of checking for errors after each statement

  Please advice,

  Abhinav

   

   

   

• Clayton Groom

  SSC-Addicted

  Points: 410

  More actions

  August 26, 2004 at 11:36 pm

  #520808

  Abhinav,

  Until we have Try/Catch blocks in T-SQL you do have to trap for errors after every proc call.  It is possible to do this in a more generic fasion by creating an error handler using a label and GOTO statements.

  You should also always capture the return code value from any procedure call to ensure that the result code is a zero (success). That is the best way to know that the call succeeded.  

  Here is a quick example of how you can build an error handler in T-SQL and keep the amount of in-line checking code to a minimum.

  ---

  create

  procedure adm_updateSomeTable

     @p_PKID

  int

    ,@p_SomeRow Varchar(30)

  as

  /*

  Example proc to demonstrate using a Label and a GOTO statement to implement an error handler

  */

  set

  nocount on

  declare

  @w_error int

  ,@w_rowcount int

  ,@w_trancount int

  ,@w_retval int

  set

  @w_retval = 0

  -- get the tran

  select

  @w_trancount = @@trancount

   

  BEGIN

  TRAN updt_table

  Update sometable set somerow = @p_SomeRow

  where pkid = @p_PKID

  select @w_error = @@error, @w_rowcount = @@rowcount

  IF @w_error <> 0 or @w_rowcount = 0 goto ERROR_HANDLER

  exec @w_retval = adm_updatesomething @p_PKID ,@p_SomeRow

  if @w_retval <> 0 goto ERROR_HANDLER

  COMMIT

  TRAN updt_table

   

  RETURN

  0

   

  ERROR_HANDLER:

  /*

  Note: you can get quite elaborate here, defining what messages get returned etc., based on the type of error.

  You will also want to consider whether or not to do a raiserror or not depending on the nesting of proc calls.

  */

  IF

  @w_trancount > @@trancount

  -- if the transaction we started is still open, roll it back

    rollback tran updt_table

   

  if

  @w_retval <> 0

  begin

    RAISERROR('ERROR executing Procedure. Return code = %d',16,1,@w_retval)

    RETURN @w_retval

  end

  else

  begin

    RAISERROR('ERROR %d UPDATING TABLE test',16,1,@w_error)

    RETURN @w_error

  end

  ---

  If you break out of a cursor loop with an errorhandler, you have to be sure to close and deallocate the cursor before you branch to the error handler label, or you will cause yourself grief...

   

  Kindest Regards,

  Clayton