You [need to add locking hints][1] to avoid constraint errors. Otherwise what can happen is that the server checks the table, then releases the lock before inserting, allowing another session to jump in. (In `SNAPSHOT` mode it won't even see the other session).

You can also use an `OUTPUT` clause, then do a conditional `SELECT` if there was no results.


```
CREATE OR ALTER PROCEDURE Saga.AddMessage
(
    @SagaIdentity NVARCHAR(256),
    @MessageId NVARCHAR(64),
    @SagaMessage NVARCHAR(MAX),
    @Created DATETIME2(7),
    @Updated DATETIME2(7),
    @SubscriptionStatus SMALLINT
)
AS

SET XACT_ABORT, NOCOUNT ON;

BEGIN TRAN;

INSERT INTO Saga.SubscribersMessages
  (SagaIdentity, MessageId, SagaMessage, Created, Updated, SubscriptionStatus)
  OUTPUT inserted.Id AS Results
SELECT
  @SagaIdentity, @MessageId, @SagaMessage, @Created, GETDATE(), @SubscriptionStatus
WHERE NOT EXISTS (SELECT 1
    FROM Saga.SubscribersMessages sm WITH (UPDLOCK, HOLDLOCK)
    WHERE sm.MessageId = @MessageId
);


IF @@ROWCOUNT = 0    
    SELECT sm.Id AS Results
    FROM Saga.SubscribersMessages sm
    WHERE sm.MessageId = @MessageId;

COMMIT;
```
Alternatively
```
SET XACT_ABORT, NOCOUNT ON;

BEGIN TRAN;

DECLARE @existingId bigint;

SELECT @existingId = sm.Id
FROM Saga.SubscribersMessages sm WITH (UPDLOCK, HOLDLOCK)
WHERE sm.MessageId = @MessageId;

IF @existingId IS NULL
BEGIN
    INSERT INTO Saga.SubscribersMessages
      (SagaIdentity, MessageId, SagaMessage, Created, Updated, SubscriptionStatus)
      OUTPUT inserted.Id AS Results
    SELECT
      @SagaIdentity, @MessageId, @SagaMessage, @Created, GETDATE(), @SubscriptionStatus
END
ELSE
BEGIN
    SELECT @existingId AS Results;
END;

COMMIT;
```
Do yourself a favour and don't quote object and column names unless absolutely necessary, it's really annoying to read.


  [1]: https://sqlperformance.com/2020/09/locking/upsert-anti-pattern