p3ace p3ace - 17 days ago 5
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
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.

Comments