Return to Revisions

You need to add locking hints to avoid constraint errors.

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.