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?
October 29, 2024 at 3:16 pm
What do you mean by "Update routine"? A stored procedure? A script? Or ORM or other C# code
If SQL code add something like
SELECT @@ROWCOUNT AS UpdatedCount
directly after the update.
Or you could modify the return code to return a non-zero value if @@ROWCOUNT is zero.
Why not pass your values to a stored procedure.
Then in the stored procedure you can do a proper upsert pattern
-- Inherent in the WHERE clause is the guarantee that we
-- can't update anything if the matching row isn't there.
UPDATE SchemaName.TableName
SET FieldName = PassedInValue
WHERE KeyField = PassedInKey;
IF ( @@ROWCOUNT = 0 ) -- Nothing was updated, so attempt to insert the value as a new record
BEGIN
-- Here we use the WHERE to ensure that we don't
-- insert a row if there's already a matching row.
INSERT INTO SchemaName.TableName ( SomeKey, SomeField )
SELECT SomeKeyValue, SomeValue
-- The hints are needed to ensure that two sessions
-- can't check the existance of the row at the same time
WHERE NOT EXISTS (SELECT 1 FROM SchemaName.TableName WITH (XLOCK, HOLDLOCK)
WHERE SomeKey = SomeKeyValue
);
END;
October 29, 2024 at 5:36 pm
I am using a Stored Procedure. I tried modifying it as you suggested and it updated the row and inserted the new ones correctly. Thanks for the help.
Here's the updated Stored Procedure:
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
October 29, 2024 at 5:46 pm
If you have multiple threads, the inserting of VALUES could result in duplicates.
I would modify this to use the WHERE NOT EXISTS
IF (@@RWOCOUNT = 0)
BEGIN
INSERT INTO dbo.tblMRBHistory ( BusinessUnit, PartNum, Description, Category, LotNum, LocationID, StockMRB, ExtendedCost, TransDate, MRBDays, Closed )
SELECT @BusinessUnit, @PartNum, @Description, @Category, @LotNum, @LocationID, @StockMRB, @ExtendedCost, @TransDate, @MRBDays, @Closed
WHERE NOT EXISTS ( SELECT 1 FROM [dbo].[tblMRBHistory] WITH (XLOCK, HOLDLOCK)
WHERE [PartNum] = @PartNum AND [LotNum] = @LotNum);
END;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply