Mysql Stored Proc not returning a VARCHAR out parameter

Question

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 1

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.