August 8, 2013 at 6:03 pm
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
August 9, 2013 at 12:05 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply