SQL Server UPDATE Command Fails without error

Using SSMS running the below UPDATE command I get returned message says 1 row affected. but if I then follow it up with a Select and it shows that the row never updated.

This issue happens in SSMS and a custom application I built in C# using Entity Framework. I have since stopped using Entity Framework generated code and moved straight to a stored proc. All of 3 different ways of performing the same task I randomly get this issue. Sometimes it can be 100’s or 1000’s of updates before the issue happens again or it could be a few updates later it happens again.
I have run UPDATE STATISTICS, I have Rebuilt Indexes and Reorganized Indexes.

As a side note, I NEVER have an issue with INSERTs only UPDATEs we never DELETE any records. and total record count on the table is 201,741

UPDATE CreativeWorkFlowQueue
    SET CurrentIndicator = 'N'
WHERE CreativeWorkFlowQueueId = 198231

SELECT *
FROM CreativeWorkFlowQueue
WHERE CreativeWorkFlowQueueId = 198231

enter image description here

here is the table

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE (dbo).(CreativeWorkFlowQueue)(
    (CreativeWorkFlowQueueId) (int) IDENTITY(1,1) NOT NULL,
    (DigitalCampaignCreativeId) (int) NOT NULL,
    (WorkFlowQueueId) (int) NOT NULL,
    (CurrentIndicator) (char)(1) NOT NULL,
    (CreateDate) (datetime) NOT NULL,
    (LastUpdatedDate) (datetime) NOT NULL,
    (IsDeleted) (bit) NOT NULL,
PRIMARY KEY CLUSTERED 
(
    (CreativeWorkFlowQueueId) ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON (PRIMARY)
) ON (PRIMARY)
GO

ALTER TABLE (dbo).(CreativeWorkFlowQueue) ADD  CONSTRAINT (CreateDate defaults to utcDate on CreativeWorkFlowQueue)  DEFAULT (getutcdate()) FOR (CreateDate)
GO

ALTER TABLE (dbo).(CreativeWorkFlowQueue) ADD  CONSTRAINT (LastUpdatedDate defaults to utcDate on CreativeWorkFlowQueue)  DEFAULT (getutcdate()) FOR (LastUpdatedDate)
GO

ALTER TABLE (dbo).(CreativeWorkFlowQueue) ADD  CONSTRAINT (DF_CreativeWorkFlowQueue_IsDeleted)  DEFAULT ((0)) FOR (IsDeleted)
GO

ALTER TABLE (dbo).(CreativeWorkFlowQueue)  WITH CHECK ADD  CONSTRAINT (CreativeWorkFlowQueue DigitalCampaignCreativeId fk) FOREIGN KEY((DigitalCampaignCreativeId))
REFERENCES (dbo).(DigitalCampaignCreative) ((DigitalCampaignCreativeId))
GO

ALTER TABLE (dbo).(CreativeWorkFlowQueue) CHECK CONSTRAINT (CreativeWorkFlowQueue DigitalCampaignCreativeId fk)
GO

ALTER TABLE (dbo).(CreativeWorkFlowQueue)  WITH CHECK ADD  CONSTRAINT (CreativeWorkFlowQueue WorkFlowQueueId fk) FOREIGN KEY((WorkFlowQueueId))
REFERENCES (dbo).(WorkFlowQueue) ((WorkFlowQueueId))
GO

ALTER TABLE (dbo).(CreativeWorkFlowQueue) CHECK CONSTRAINT (CreativeWorkFlowQueue WorkFlowQueueId fk)
GO

EDIT Adding the Stored Proc that also has the same issue.

I did not include this stored proc simply due to the fact that it is complex and if a simple update doesn’t work I figured start there first.

CREATE PROCEDURE (dbo).(sp_WtvToRtl) 
    -- Add the parameters for the stored procedure here
    @tbl DCCIdList READONLY,    
    @WorkFlowQueueId int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    
    DECLARE @dccList TABLE (
        CreativeWorkflowQueueId int null,
        DigitalCampaignCreativeId int null,
        WorkFlowQueueId int null,
        CurrentIndicator varchar(1) collate SQL_Latin1_General_CP1_CI_AS null,
        LastUpdatedDate datetime null
    )

    DECLARE @DCIDList TABLE(
        DigitalCampaignCreativeId int null
    )





        DECLARE @res TABLE (
        MergeAction VARCHAR(50) collate SQL_Latin1_General_CP1_CI_AS ,
        CreativeWorkFlowQueueId INT, 
        _Position INT
        )

    BEGIN TRANSACTION t1

    BEGIN TRY
        INSERT INTO @dccList (CreativeWorkflowQueueId, DigitalCampaignCreativeId,LastUpdatedDate,WorkFlowQueueId,CurrentIndicator)
        select CreativeWorkflowQueueId, DigitalCampaignCreativeId,LastUpdatedDate,WorkFlowQueueId, 'N' as CurrentIndicator from 
        dbo.CreativeWorkFlowQueue Where CurrentIndicator = 'Y' 
        and IsDeleted = 0
        and DigitalCampaignCreativeId In 
        (SELECT DigitalCampaignCreativeId FROM VRF.DigitalCampaignCreative 
        WHERE IsDeleted = 0 and SfClosed = 0 
        and DccId in (Select DccId COLLATE SQL_Latin1_General_CP1_CI_AS from @tbl)
        )
         
         INSERT INTO @DCIDList 
         SELECT DigitalCampaignCreativeId FROM @dccList
         GROUP BY DigitalCampaignCreativeId

        INSERT INTO @dccList (DigitalCampaignCreativeId,WorkFlowQueueId,CurrentIndicator)
        select DigitalCampaignCreativeId, @WorkFlowQueueId as WorkFlowQueueId, 'Y' as CurrentIndicator from 
        @DCIDList


        MERGE vrf.CreativeWorkFlowQueue a
        USING 
            (
            SELECT 
                x.CreativeWorkflowQueueId,
                x.DigitalCampaignCreativeId,
                x.LastUpdatedDate,
                x.WorkFlowQueueId,
                x.CurrentIndicator 
            FROM @dccList x
            ) stg  
    
        ON stg.CreativeWorkflowQueueId = a.CreativeWorkflowQueueId

        WHEN MATCHED THEN
            UPDATE SET 
                a.CurrentIndicator  = stg.CurrentIndicator,
                a.LastUpdatedDate = getutcdate()
        WHEN NOT MATCHED THEN
            INSERT 
                (
                    CurrentIndicator,
                    DigitalCampaignCreativeId,
                    WorkFlowQueueId
                )
            VALUES 
                (
                    stg.CurrentIndicator, 
                    stg.DigitalCampaignCreativeId, 
                    stg.WorkFlowQueueId
                )  
            -- ********************************************************************
            -- Remove this if you don't need to return the results below.
            OUTPUT 
                INSERTED.CreativeWorkFlowQueueId,
                $action 
            INTO @res (CreativeWorkFlowQueueId, MergeAction);
            -- ********************************************************************


        COMMIT TRANSACTION t1

    END TRY
    BEGIN CATCH

        DECLARE @ErrorMessage NVARCHAR(MAX), @ErrorSeverity INT, @ErrorState INT;
        SELECT @ErrorMessage = ERROR_MESSAGE() + ' Line ' + CAST(ERROR_LINE() AS NVARCHAR(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
        ROLLBACK TRANSACTION t1
        --RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
        SELECT 'FAILED' MergeAction,
                s.CreativeWorkflowQueueId,
                s.DigitalCampaignCreativeId,
                '' DccId,
                s.WorkFlowQueueId,
                s.CurrentIndicator,
                null CreateDate,
                null LastUpdatedDate,
                null IsDeleted,
                @ErrorMessage ErrorMessage
        FROM @dccList s
    END CATCH


    -- Normally, I would not have this.  But this will simulate
    -- what EF does if you need it.
    SELECT 
        r.MergeAction,
        a.CreativeWorkFlowQueueId,
        a.DigitalCampaignCreativeId,
        d.DccId,
        a.WorkFlowQueueId,
        a.CurrentIndicator,
        a.CreateDate, 
        a.LastUpdatedDate,
        a.IsDeleted,
        '' ErrorMessage
    FROM vrf.CreativeWorkFlowQueue a 
    JOIN @res r ON a.CreativeWorkFlowQueueId = r.CreativeWorkFlowQueueId
    JOIN vrf.DigitalCampaignCreative d ON a.DigitalCampaignCreativeId = d.DigitalCampaignCreativeId
    ORDER BY 
        r.MergeAction,
        r._Position


END

The User-Defined Table Type is this.

CREATE TYPE (dbo).(DCCIdList) AS TABLE(
    (DccId) (nvarchar)(18) NULL
)