Issue with No of rec isnerted and Error count in Sp

• mcfarlandparkway

  SSCertifiable

  Points: 7699

  More actions

  March 29, 2017 at 6:20 pm

  #329001

  I have a stored procedure where it just do updates; I am tracking the updated records into @NoOfRecsInserted  and if any error occurs it catches and keeps in @ErrorRows  count , But this code I am not getting correct count? What is the issue in the code?

  ALTER PROCEDURE [dbo].[GetStd]
  AS

  BEGIN
   SET NOCOUNT ON

  DECLARE

   @TargetRows INT,
          @ErrorRows INT,
          @NoOfRecsInserted INT

  SET @TargetRows = 0
  SET @ErrorRows = 0
  SET @NoOfRecsInserted = 0

  BEGIN TRY

   UPDATE ED
   SET ED.ID = ET.SID,
    ED.Code = ET.TCode,
    FROM dbo.Emp ED
    INNER JOIN dbo.EXt ET
      ON ED.Num = ET.Num
   

   SET @NoOfRecsInserted = @NoOfRecsInserted + 1; END TRY
   BEGIN CATCH
   SET @ErrorRows = @ErrorRows + 1;   --Log error into ErrorTable
     Exec InLogError 'ProcName', NULL
     
      END CATCH

   SET @TargetRows = @NoOfRecsInserted

  END

• Johan Bijnens

  SSC Guru

  Points: 135259

  More actions

  March 30, 2017 at 1:01 am

  #1935641

  ..

  UPDATE ED
SET ED.ID = ET.SID,
ED.Code = ET.TCode,
FROM dbo.Emp ED
INNER JOIN dbo.EXt ET
  ON ED.Num = ET.Num

  SET @NoOfRecsInserted = @NoOfRecsInserted + @@rowcount;

  ..

  btw: you're  performing an UPDATE ....  @NoOfRecsInserted may be a confusing name
  

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me