p3ace p3ace - 4 months ago 11
MySQL Question

Assigning variable value to out parameter in mysql

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

Answer

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