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

`var_registrationId` is different from `@var_registrationId`. — Blank, Aug 04 '16 at 01:21
@Drew I removed the @ sign I guess I'm confused of how it's used and when to use it and how it's different without the @ sign. — heisenberg, Aug 04 '16 at 01:49
This is the difference: http://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference — Ash, Aug 04 '16 at 01:54
Thanks everyone. I finally fixed it. However, could you tell me if my understanding of the differences is correct. Is @variable exists like a temporary variable where you don't need to use DECLARE keyword unlike variable(without @) which is declared as "DECLARE variable INT;" Does that mean i can create and refer to any number of @variable(s) anywhere in a stored proc without the DECLARE keyword? Thanks. =) — heisenberg, Aug 04 '16 at 02:05

Drew · Accepted Answer · 2016-08-04T02:13:23.380

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

thanks again. I spelled it wrong, should've been ID() =P Procedure actually has several lines. Anyways, I'll make use of your answer as reference. I'm working on an Enrollment System and I had to do both the frontend and backend so thanks. — heisenberg, Aug 04 '16 at 02:10

Assigning variable value to out parameter in mysql

1 Answers1