Calculating rows processed and updated

• Oracle765

  SSCrazy

  Points: 2862

  More actions

  August 8, 2013 at 6:03 pm

  #276739

  Hi Professionals

  I am running a procedure to calculate the total number of rows updated but I also wan the number of rows it hasnt updated, could I be going wrong somewhere would I need to calculated a select count(*) from the table minus the total rows updated or something.

  anyway here is my procedure

  USE [TestData]

  GO

  /****** Object: StoredProcedure [dbo].[cleanseSMPN] Script Date: 08/09/2013 09:25:30 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  ALTER procedure [dbo].[cleanseSMPN]

  as

  begin

  declare @TotalRows int =0, @numrows int;

  --Update the Software Manufacturer and the Product name

  begin

  truncate table myupdates

  UPDATE dbsource SET softwaremanufacturer = dbref.Amended_SW_Manufacturer,

  productname = dbref.Amended_Product_Name

  FROM dbo.newtable dbsource

  INNER JOIN (

  SELECT Raw_SW_Manufacturer,Amended_SW_Manufacturer,Raw_Product_Version,Amended_Product_Version,

  Raw_Product_Name,Amended_Product_Name

  FROM datalookuptable

  GROUP BY Raw_SW_Manufacturer,Amended_SW_Manufacturer,Raw_Product_Version,Amended_Product_Version,

  Raw_Product_Name,Amended_Product_Name

  ) dbref

  ON dbref.Raw_SW_Manufacturer = softwaremanufacturer

  --and dbref.Raw_Product_Version = dbsource.productversion

  and dbref.Raw_Product_Name = productname

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

  set @NumRows = @@ROWCOUNT;

  set @TotalRows= @TotalRows + 1;

  insert into myupdates(NumRows,TotalRows)

  values (@NumRows,@TotalRows)

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

  end

  END

• Koen Verbeeck

  SSC Guru

  Points: 259165

  More actions

  August 9, 2013 at 12:05 am

  #1640042

  Right now @TotalRows will always be 1 at the end. Is that the idea?

  If you want the rows not updated, you'll need to do a rowcount (there are many ways to do this) and substract @numrows from it.

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP