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