SP returning Error code to another SP

• Sathish Kumar. Rajaram

  Mr or Mrs. 500

  Points: 581

  More actions

  March 22, 2006 at 3:57 am

  #116649

  Hi

   

  I have a list of SP's. This SP is listed in a table.

  I execute a Master SP, which will loop thru the table,

  fetch the SP name and execute them one by one.

   

  All my individual SP's will return the error code as output parameter.

  Now when I execute the Master SP i throws me an error.

   

  Pls advice me how i can trap this error code in master SP

   

  Code Snippet

  ============

  Individual SP

  -------------

  CREATE PROCEDURE usp_Process01

      @sCode VARCHAR(20) , @ErrCode Integer OUTPUT

  AS

  BEGIN

   

      UPDATE ProductDetails

      SET 

          ProductCategory = 'STAT'

      WHERE 

          Product_Code is null

   

      SET @ErrCode = @@ERROR

      IF (@ErrCode = 0) 

          COMMIT TRANSACTION

      ELSE

          ROLLBACK TRANSACTION

   

      RETURN @ErrCode

   

  END

   

  Master SP

  ---------

  This is what i have given in the LOOP

   

  DECLARE @ErrCode INTEGER

  WHILE @@FETCH_STATUS = 0

  BEGIN

     

      SELECT @sSQL = 'EXEC ' + @sProcess_SP + ' ''' + @sCode + ''', @ErrCode OUTPUT'

    

      EXEC (@sSQL)

   

      --If Error then Exit out of the Loop

      IF @ErrCode <> 0 BREAK

  END

   

  When I execute the Master SP, it says

  Server: Msg 137, Level 15, State 2, Line 1

  Must declare the variable '@ErrCode'.

   

  S A T ...
  Sathish's Blog

• MVDBA (Mike Vessey)

  SSC-Insane

  Points: 21797

  More actions

  March 22, 2006 at 5:13 am

  #628052

  i think you might have few problems doing it this way

  1) the value you return from the child procedure is retuned as an output variable so you need to call it like this (see books on line)

  declare @results integer

  exec sp_myproc @errcode=@results OUTPUT

  secondly - as your buildig string and calling the resultant string using EXEC() then any variables you declare or anythinig inside that EXEC block go out of scope after you finish the exec block.

  for example

  declare @strsql navrchar(100)

  set @strsql='declare @fred int

  set @fred=1

  '

  exec @strsql

  select @fred

  doens't work because @fred is created inside a diferrent thread and then destroyed when the exec completes.

  MVDBA