Assigning variable value to out parameter in mysql

Question

Both insert statements are successful and inserts correctly on table registration and registration_header. My only problem is, it does not return the correct id through the myOutParameter.

It's not picking up the value of @var_registrationId using SET myOutParameter = @var_registrationId

Is my syntax wrong? I know i can set it using SET

CREATE DEFINER=`root`@`localhost` PROCEDURE `register`(IN parameter1 INT, IN parameter2 INT, OUT myOutParameter INT)

        BEGIN

        DECLARE var_registrationId INT;
        DECLARE EXIT HANDLER FOR sqlexception
            BEGIN
                ROLLBACK;
                RESIGNAL;
            END;

        START TRANSACTION;
        -- first insert to registration table which generates a Primary key auto increment id
        INSERT INTO registration(col1,col2) VALUES (parameter1, parameter2);

        SELECT LAST_INSERT_VALUE() INTO var_registrationId; -- id of insert on registration table

        insert into registration_header(registrationId,column)
        VALUES(@var_registrationId,parameter1);

        -- the next statement is not assigning the value of var_registrationId to the myOutParameter using SET

    SET myOutParameter = @var_registrationId; -- this isn't working and returns 0

COMMIT;

    END

I don't know what's wrong.

I hope you can help.

Thanks in advance

Answer 1

Schema:

create schema blahx8; -- create a new db so as not to screw yours up
use blahx8; -- use the new db

-- drop table if exists registration;
create table registration
(   id int auto_increment primary key,
    col1 int not null,
    col2 int not null
);

-- drop table if exists registration_header;
create table registration_header
(   id int auto_increment primary key,
    registrationId int not null,
    `column` int not null -- pretty bad column name. Use back-ticks
);

Stored Proc:

DROP PROCEDURE IF EXISTS `register`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `register`
(   IN parameter1 INT, 
    IN parameter2 INT, 
    OUT myOutParameter INT
)
BEGIN

    DECLARE var_registrationId INT;
    DECLARE EXIT HANDLER FOR sqlexception
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;
    -- first insert to registration table which generates a Primary key auto increment id
    INSERT INTO registration(col1,col2) VALUES (parameter1, parameter2);
    SELECT LAST_INSERT_ID() INTO var_registrationId; -- id of insert on registration table

    insert into registration_header(registrationId,`column`)
    VALUES(registrationId,parameter1);

    SET myOutParameter = var_registrationId; -- This is now happy
    COMMIT;
    SET @still_Alive=7; -- watch this thing !! Be careful
END$$
DELIMITER ;

Test:

SET @thisThing=-1;
CALL register(7,8,@thisThing);
select @thisThing; -- 1
CALL register(7,8,@thisThing);
select @thisThing; -- 2
CALL register(7,8,@thisThing);
select @thisThing; -- 3

select @still_Alive; -- 7
-- yikes, be carefull with User Variables. They are connection-based
-- still alive out here outside of stored proc (unlike Local Vars)

Cleanup:

drop schema blahx8; -- drop new db, poof gone

A Local Variable (from a DECLARE) is not the same as a nearly similar User Variable (with an @ sign).

I also fixed the LAST_INSERT_ID().