Debugging Stored Procedure in Visual Studio – Object Reference Not Set Error

debuggingMySQLstored-procedures

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!

Best Answer

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

Related Question