Return to Revisions

You need to add locking hints 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.