TRY .. CATCH sometimes fails to catch PRIMARY KEY VIOLATION I wrote code to upsert records to the database. The database is Azure DB, S4 tier.

Updates and inserts are sometimes are highly concurrent since it's coming from the application that doing too many updates.

The code works successfully most of the times, but sometimes it throws 2627 exception (that handled inside of the CATCH statement). Cannot figure out so far what could be the issue.

I can replace it with MERGE statement, but I tbh don't favor it much, and also want to figure out what's happening with this try and catch.

SET nocount ON;
IF NOT EXISTS(SELECT * FROM MyTable WHERE ID = @ID)
BEGIN TRY
INSERT INTO Table(ID, Value, TimeChanged) VALUES (@id, 'xxx', GETDATE())
END TRY
BEGIN CATCH
IF @@Error &lt;&gt; 2627 ----Violation of PRIMARY KEY constraint
THROW;
END CATCH

UPDATE MyTable SET Value = 'xxx' WHERE ID = @ID AND TimeChanged &lt; GETDATE()
Not sure if this is the issue, but you should use ERROR_NUMBER() not @@error in your catch block.

In any case something like this should work reliably, without PK voliations:

SET nocount ON;
begin transaction
IF NOT EXISTS(SELECT * FROM MyTable with (updlock, holdlock) WHERE ID = @ID)
begin
  INSERT INTO Table(ID, Value, TimeChanged) VALUES (@id, 'xxx', GETDATE())
END 
else
begin
  UPDATE MyTable SET Value = 'xxx' WHERE ID = @ID AND TimeChanged &lt; GETDATE()
end
commit transaction