1

I'm working on updating a stored procedure that is called to update a user. This stored procedure needs to also create a description of what was updated for auditing purposes. However when debugging in Visual Studio I get!

screenshot

All of my input values are valid, I leave none null, and my update_* procedures without the action_taken value are working. I'm seemingly writing something incorrectly, but I haven't figured out what.

CREATE DEFINER=`root`@`localhost` PROCEDURE `update_user`(
in requestor_id BIGINT(20),
in user_id BIGINT(20),
in department_id BIGINT(20),
in location_id BIGINT(20),
in shift_id BIGINT(20),
in first_name VARCHAR(45),
in last_name VARCHAR(45),
in email VARCHAR(45),
in phone VARCHAR(45),
in security_stamp LONGTEXT
)
BEGIN
    DECLARE queried_username VARCHAR(45);
    DECLARE action_taken VARCHAR(255);
    SET action_taken = '';

    SELECT
        users.username
    INTO
        queried_username
    FROM users 
    WHERE users.id=user_id
    LIMIT 1;

    IF (queried_username IS NULL) THEN
    BEGIN
    END;

IF (department_id != users.department_id) THEN
    SET action_taken = CONCAT(action_taken, '[Department Updated]');
END IF;
IF (location_id != users.location_id) THEN
    SET action_taken = CONCAT(action_taken, '[Location Updated]');
END IF;
IF (shift_id != users.shift_id) THEN
    SET action_taken = CONCAT(action_taken, '[Shift Updated]');
END IF;
IF (first_name != users.first_name) THEN
    SET action_taken = CONCAT(action_taken, '[First Name Updated]');
END IF;
IF (last_name != users.last_name) THEN
    SET action_taken = CONCAT(action_taken, '[Last Name Updated]');
END IF;
IF (email != users.email) THEN
    SET action_taken = CONCAT(action_taken, '[Email Updated]');
END IF;
IF (phone != users.phone) THEN
    SET action_taken = CONCAT(action_taken, '[Phone Updated]');
END IF;

    BEGIN
        UPDATE users SET 
            users.department_id=department_id, 
            users.location_id=location_id, 
            users.shift_id=shift_id,
            users.first_name=first_name,
            users.last_name=last_name,
            users.email=email,
            users.phone=phone,
            users.modified=NOW(),
            users.security_stamp=security_stamp
        WHERE users.id=user_id;

        CALL update_audit_log(
            requestor_id, 
            user_id,
            null,
            department_id, 
            location_id, 
            shift_id, 
            null,
            "User Administration", 
            CONCAT(
                'Modified user (id="',
                user_id,
                '", username="',
                queried_username,
                '") | Action= ',
                action_taken
            )
        );
    END;
    END IF;
END

Any help is very much appreciated!

0

Do you have DELIMITER statements around the CREATE...?

Does VS have any way to debug Stored Routines? (I suspect not.)

The failure to do it this way could cause trouble later:

START TRANSACTION;
SELECT ... from users ... FOR UPDATE;
various SETs
UPDATE ...;
COMMIT;

Another consideration... An "audit trail" may be better achieved via a TRIGGER wherein you can compare NEW. to OLD..

  • No DELIMITER, and VS 2013 at least does have Stored Procedure Debugging. In Server Explorer, you can right click > Debug Stored Procedure and enter parameters. It will then execute the SP and if you have breakpoints set it will allow you to step through. I can step through the other procedures, just not this one although the mysql is validated in mysql workbench. – John M. Aug 19 '15 at 21:16
  • Thanks for your help though. I found that the delimiter declaration was added when the script was applied through MySQL workbench 6.3 – John M. Aug 20 '15 at 14:58
0

I figured it out. The comparisons in the IF statements needed to be to a queried variable, e.g.:

SELECT
    users.username
INTO
    queried_username
FROM users 
WHERE users.id=user_id
LIMIT 1;

Then you can compare using NOT LIKE like:

IF (username NOT LIKE queried_username) THEN
    SET action_taken = CONCAT(action_taken, '[Username Updated]');
END IF;

Don't forget to DECLARE each variable before assigning them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.