p3ace p3ace - 1 year ago 71
MySQL Question

Mysql Stored Proc not returning a VARCHAR out parameter

Below is my stored procedure. It works fine but my problem is I can't get the output parameter as

VARCHAR
.

The part where I'm having problem is the assignment of
@curcName
to the out parameter
op_resultMessage


BEGIN
SET op_resultMessage = @curcName;
END;


Here's the Stored Procedure.

CREATE DEFINER=`root`@`localhost` PROCEDURE `addCurriculum`(
IN p_curcName varchar(100),
IN p_description TEXT,
IN p_yearLevel VARCHAR(50),
IN p_syStart INT,
IN p_syEnd INT,
IN p_creator VARCHAR(50),
OUT op_resultMessage VARCHAR(50))
BEGIN

DECLARE curcName VARCHAR(20) ;

IF EXISTS
(SELECT @curcName := `name`
FROM curriculum
WHERE
yearLevel = p_yearLevel
AND syStart = p_syStart
AND syEnd = p_syEnd )

THEN --

BEGIN
SET op_resultMessage = @curcName;
END;

ELSE

BEGIN
INSERT INTO curriculum(`name`, description, yearLevel, syStart, syEnd, creator)
VALUES(p_curcName,p_description,p_yearLevel,p_syStart,p_syEnd,p_creator);
END;

END IF;

END


I'm trying to return a message IF
name
EXISTS

So it should go something like

SET op_resultMessage = @curcName 'already uses the school year and year level you're trying to insert';


But I don't know how to properly concatenate and assign values. I'm still confused with
:= SET
and
=
operators. I guess that's where I'm having problems with.


If I change the out parameter's type to an
INT
like

OUT op_resultMessage VARCHAR(50)


then assigns a number to
op_resultMessage
like
SET op_resultMessage = 1;


It returns the number 1 as out parameter values. It just won't work with varchar.

So when I try to call the procedure

CALL `enrollmentdb`.`addCurriculum`
('Test Curriculum ','Test ','Grade 1',2015,2016,'jordan',@outputMsg);

SELECT @outputMsg; -- this doesn't return any value even if Grade 1, 2015 and 2016 exists


I'd appreciate any help. I actually just learned mysql recently.

Thanks.

Answer Source
drop procedure if exists addCurriculum;
delimiter $$
CREATE   PROCEDURE `addCurriculum`(    
    IN p_curcName varchar(100), 
    IN p_description TEXT, 
    IN p_yearLevel VARCHAR(50),
    IN p_syStart INT, 
    IN p_syEnd INT, 
    IN p_creator VARCHAR(50),
    OUT op_resultMessage VARCHAR(50))
BEGIN

    DECLARE curcName VARCHAR(20) ;

    SELECT `name` into @curcName
        FROM curriculum 
        WHERE 
            yearLevel       =       p_yearLevel 
            AND syStart     =       p_syStart 
            AND syEnd       =       p_syEnd
            LIMIT 1;
    -- Note change above. When selecting into a variable (or more than 1)
    -- then 0 or 1 rows can come back max or an Error occurs

    IF @curcName is not null then
        SET op_resultMessage = @curcName;
    ELSE
        BEGIN
            INSERT INTO curriculum(`name`, description, yearLevel, syStart, syEnd, creator)
            VALUES(p_curcName,p_description,p_yearLevel,p_syStart,p_syEnd,p_creator); 
        END;
        SET op_resultMessage = 'GEEZ I am right here'; -- Drew added this
    END IF;
END$$
delimiter ;

Note the commentary in the stored procedure, especially the part of only 0 or 1 rows returning else an Error will occur with a select into var pattern. So LIMIT 1. That may or may not be the row you want (limit 1), but that is where it is at right now.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download