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...