October 31, 2007 at 8:40 am
Disclaimer: I am a web developer, not a sql guy so I'll take all the help I can get!
I need to update information for a user and if the user is classified as a primary (@blnPrimary) then I need to update information for all users within his agency (AgencyUniqueId). The issue is that the second UPDATE to "cdds_User_Profile" always returns a rowcount of 0 (should be 1) even though the values for "@Original_AgencyUniqueId" and "@Original_UserId" are correct. This is just a snippet of the whole procedure. I'm trying to implement similar logic in other parts of the procedure and I'm observing the same behavior there as well. Any help anyone can provide is greatly appreciated.
/*** Update User Profile ***/
UPDATE [cdds_User_Profile]
SET [FirstName] = @FirstName, [LastName] = @LastName, [Title] = @Title,
[Phone] = @Phone, [AcctType] = @AcctType, [AcctStatus] = @AcctStatus,
[LastUpdatedDate] = GETDATE()
WHERE ([FirstName] = @Original_FirstName AND [LastName] = @Original_LastName
AND [Title]=@Original_Title AND [Phone]=@Original_Phone
AND [AcctType]=@Original_AcctType AND [AcctStatus]= @Original_AcctStatus
AND [AgencyUniqueId] = @Original_AgencyUniqueId
AND [UserId] = @Original_UserId);
IF @@ROWCOUNT = 0
BEGIN
SET @err_message = 'Data has been edited by another user since you began viewing this information.'
RAISERROR (@err_message,11, 1)
ROLLBACK TRANSACTION
RETURN
END
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
IF @blnPrimary = 1
BEGIN
IF LOWER(@AcctStatus) <> LOWER(@AgencyAcctStatus)
/*** Update Users Acct. Status ***/
/* update all users in same agency profile */
UPDATE [cdds_User_Profile]
SET [AcctStatus] = @AcctStatus,[LastUpdatedDate] = GETDATE()
WHERE ([AgencyUniqueId] = @Original_AgencyUniqueId
AND [UserId] = @Original_UserId);
IF @@ROWCOUNT = 0
BEGIN
SET @err_message = 'Data for this agency has been edited by another user since you began viewing this information.'
RAISERROR (@err_message,11, 1)
ROLLBACK TRANSACTION
RETURN
END
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
END
October 31, 2007 at 1:45 pm
These are kind of contradicting each other:
/* update all users in same agency profile */
....
AND [UserId] = @Original_UserId);
Second update does not look right to me at all.
Not because of SQL but because of logic in it.
_____________
Code for TallyGenerator
October 31, 2007 at 1:49 pm
Basically I added BEGIN and END and modified the error handling. Here is the corrected and functioning code for the second update statement:
/*** Update Users Acct. Status ***/
/* update all users in same agency profile */
IF LOWER(@AcctStatus) <> LOWER(@AgencyAcctStatus)
BEGIN
UPDATE [cdds_User_Profile]
SET [AcctStatus] = @AcctStatus,[LastUpdatedDate] = GETDATE()
WHERE ([AgencyUniqueId] = @Original_AgencyUniqueId
AND [UserId] <> @Original_UserId)
IF (@@ROWCOUNT = 0 OR @@ERROR <> 0)
BEGIN
SET @err_message = 'Data for this agency has been edited by another user since you began viewing this information.'
RAISERROR (@err_message,11, 1)
ROLLBACK TRANSACTION
RETURN
END
END
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply