How can I tell if an UPDATE command updated any rows?

Transact-SQL
1

I am calling an Update routine from C# and need to determine if the Update routine found a matching row to update. If it does not, I call the Insert Routine. I was hoping that if no row was updated, I would get an error which would tell me that I need to do the insert.

Is there a way to determine if a row was updated?

2

After working the Update function, you may want to see how many rows are effected. In a lot of databases, the return value—typically zero—will show that no rows have been changed. You can safely call your Insert method if that's the case!

3

Someone in another forum suggested that I do the update AND the insert in the same Stored Procedure. Here's the Stored Procedure I ended up with:

CREATE PROCEDURE [dbo].[insupd_tblMRBHistory]
   (@BusinessUnit char(30)
           ,@PartNum char(25)
   ,@Description varchar(50)
   ,@Category char(4)
   ,@LotNum char(25)
   ,@LocationID int
   ,@StockMRB int
   ,@ExtendedCost decimal(7,2)
   ,@TransDate datetime
   ,@MRBDays int
   ,@Closed bit)
AS
BEGIN
UPDATE [dbo].[tblMRBHistory]
SET        [BusinessUnit] = @BusinessUnit,
           [PartNum] = @PartNum,
   [Description] = @Description,
           [Category] = @Category,
   [LotNum] = @LotNum,
   [LocationID] = @LocationID,
   [StockMRB] = @StockMRB,
   [ExtendedCost] = @ExtendedCost,
   [MRBDays] = @MRBDays,
   [Closed] = @Closed
WHERE [PartNum] = @PartNum AND [LotNum] = @LotNum

IF (@@RWOCOUNT = 0)
INSERT INTO [dbo].[tblMRBHistory]
           ([BusinessUnit],
           [PartNum],
   [Description],
           [Category],
   [LotNum],
   [LocationID],
   [StockMRB],
   [ExtendedCost],
   [TransDate],
   [MRBDays],
   [Closed])
     VALUES
           (@BusinessUnit,
           @PartNum,
   @Description,
           @Category,
   @LotNum,
   @LocationID,
   @StockMRB,
   @ExtendedCost,
   @TransDate,
   @MRBDays,
   @Closed)
END
4

you may also consider using merge statement