Delete Works in one Stored Procedure but not another

Question

I've built an app that uses stored procedures to insert into, update, and delete rows from tables in my database. All domain users have been given execute permissions on these procedures, all procedures have the same permissions on them.

Two of those procedures contain the same delete statement:

DELETE RFT
FROM Database.schema.tbl_Review_Response_FreeText RFT
WHERE RFT.Instance_ID = @InstanceID

There will only ever be one row per InstanceID. In one procedure, the statement deletes the appropriate row, in the other it doesn't.

With regards to the statement that doesn't work, I don't think @InstanceID is losing its value because an update statement uses it right before without any issues.

It doesn't seem like a permissions issue because the delete works in one of the procedures but not the other (for the user), but it does seem like a permissions issue because when I execute the procedures both work as intended. So that makes me think it's something else, but Google has been unhelpful.

Any ideas as to what might be going on here?

---

Clarification:

The row exists prior to the delete statement (and is still there after the delete), and the update is updating a different table.

Here's full definition of the procedure that isn't deleting:

USE [database]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [schema].[spr_App_EngRvwAddProxyRow]
    @ProxyNm varchar(100),@InstanceID bigint
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @UserOprID varchar(8)
    DECLARE @ProxyID varchar(8)

    SET @UserOprID=RIGHT(SUSER_SNAME(), LEN(SUSER_SNAME()) - CHARINDEX('\', SUSER_SNAME()))

    SET @ProxyID=   (
                    SELECT TOP 1 ep.Opr_ID ProxyToID
                    FROM database.schema.tbl_Reviews r
                        INNER JOIN database.schema.tbl_Review_Setup rs ON rs.Rvw_ID=r.Rvw_ID
                        INNER JOIN database.schema.tbl_Engagement_Proxies ep ON ep.Eng_ID=r.Eng_ID AND ep.Qtr_ID=rs.Qtr_ID
                        LEFT OUTER JOIN Import_Stage_Db.schema.vw_ALLWb_Assoc_Identifiers AS aaProx1 ON aaProx1.OPER_ID = ep.Opr_ID 
                    WHERE 
                    --(REPLACE(@ProxyNm,'.',',')=REPLACE(aaProx1.PREFERRED_NAME,)
                    @ProxyNm=REPLACE(aaProx1.PREFERRED_NAME,',','.')
                        AND 
                        r.Instance_ID=@InstanceID
                        AND 
                        r.Rvwer_OprID=@UserOprID
                        AND 
                        r.ADD_DTTM= (
                                        SELECT MAX(r2.ADD_DTTM)
                                        FROM database.schema.tbl_Reviews r2
                                        WHERE r2.Instance_ID=r.Instance_ID
                                        )
                    )
    --Insert a new row into the reviews table for the proxy
    INSERT INTO database.schema.tbl_Reviews(Instance_ID,Rvw_ID,Eng_ID,Rvwee_OprID,Rvwer_OprID)
    SELECT r.Instance_ID,r.Rvw_ID,Eng_ID,r.Rvwee_OprID,@ProxyID
    FROM database.schema.tbl_Reviews r
    WHERE r.Instance_ID=@InstanceID
        AND r.Rvwer_OprID=@UserOprID
        AND r.ADD_DTTM= (
                        SELECT MAX(r2.ADD_DTTM)
                        FROM database.schema.tbl_Reviews r2
                        WHERE r2.Instance_ID=r.Instance_ID
                        )

    --Wipe any responses for that reviewee from the repsonses table
    UPDATE database.schema.tbl_Review_Response
    SET Response_ID = NULL, LAST_UPD_BY = NULL, LAST_UPD_DTTM = NULL
    WHERE Instance_ID = @InstanceID

    --Wipe the free text reponses for the instance ID
    DELETE RFT
    FROM database.schema.tbl_Review_Response_FreeText RFT
    WHERE RFT.Instance_ID = @InstanceID

    --Always return a row for the front end
    SELECT 0 AS Return_Value
END

And here's the first part of the procedure that's working (up until the delete):

USE [database]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [schema].[spr_App_EngRvwResponse_Upd]
    @QID int,@RtgTxt varchar(max),@IID int
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    DECLARE @ScaleDtlID tinyint,@IsFreeTxt bit,@UserOprID varchar(8)


    SET @UserOprID=RIGHT(SUSER_SNAME(), LEN(SUSER_SNAME()) - CHARINDEX('\', SUSER_SNAME()))


    SELECT TOP 1 
        @ScaleDtlID=sd.Scale_Dtl_ID
        ,@IsFreeTxt=CASE WHEN q.Scale_ID=1
                        THEN 1
                        ELSE 0
                        END 
    FROM database.schema.tbl_Questions q
        LEFT OUTER JOIN database.schema.tbl_Rvw_Scale_Detail sd 
            ON sd.Scale_ID=q.Scale_ID
                AND sd.Label_Text=@RtgTxt
    WHERE q.Q_ID=@QID


    IF @IsFreeTxt=1
        BEGIN
            IF ltrim(rtrim(@RtgTxt))='' OR @RtgTxt IS NULL
                BEGIN
                    DELETE rft
                    FROM database.schema.tbl_Review_Response_FreeText rft
                    WHERE rft.Instance_ID=@IID
                        AND rft.Q_ID=@QID
                END
            ELSE
   --rest of code

I think it's worth noting that ISOLATION LEVEL was the same on both until a co-worker suggested I remove it on the procedure with the delete that isn't working...

Answer 1

Was able to figure out the issue.

I combed back through the app and asked the user again how he was kicking off the process. The issue was that the user wasn't saving the value before kicking off the process, so a separate process was running to save unsaved values and was inserting the value into the table after the delete statement ran.

I modified the code in my app to delete the value (from the app) before the unsaved values in the app are saved.

Thanks to @spaghettidba for nudging me in the right direction!