error code 1414 in mysql when i am call store procedure

Question

any body help me.. i am call sp not succses,

--==================================================================================
Query: call `sp_MasterDataPegawai`('','0123555','neni','P','001','001',1,'',null) 

Error Code: 1414
OUT or INOUT argument 9 for routine @maninds_std_mwt.sp_MasterDataPegawai is not a variable or NEW pseudo-variable in BEFORE trigger
--==================================================================================

and this is my sp :

--==================================================================================

DELIMITER $$

USE `@maninds_std_mwt`$$

DROP PROCEDURE IF EXISTS `sp_MasterDataPegawai`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_MasterDataPegawai`(
    IN p_IdPegawai CHAR(10),
    IN NIK CHAR(10),
    IN p_NamaLengkap VARCHAR(50),
    IN p_JenisKelamin CHAR(1),
    IN p_KdDivisi CHAR(3),
    IN p_KdJabatan CHAR(3),
    IN p_KdStatusAktif TINYINT(1),
    IN p_status CHAR(1),
    OUT OutputId VARCHAR(10)
    )
BEGIN
    /* ======Local variabel==========*/
    DECLARE pLoc_TempIdPegawai  VARCHAR(10);
    DECLARE pLoc_TempIdPegawai_i INTEGER;
    DECLARE i INTEGER;
    DECLARE pLoc_KdTitle CHAR(2);
    DECLARE pLoc_KdJenisPegawai CHAR(3);
    /*===============================*/

    IF p_JenisKelamin = 'L' THEN 
        SET pLoc_KdTitle = '01';
    ELSE
        SET pLoc_KdTitle = '02';
    END IF;

    SET pLoc_KdJenisPegawai = '001';

    SELECT pLoc_TempIdPegawai = `IdPegawai`,COUNT(*) FROM `tbl_data_pegawai` WHERE `IdPegawai` = p_IdPegawai;
    IF COUNT(*) = 0 THEN

            SELECT pLoc_TempIdPegawai_i = MAX(RIGHT(`IdPegawai`,6)) FROM `tbl_data_pegawai` WHERE `IdPegawai` <> '7777777777';
            IF pLoc_TempIdPegawai_i IS NULL THEN

                SET pLoc_TempIdPegawai = CONCAT(p_JenisKelamin,pLoc_KdJenisPegawai,'000001');

            ELSE

                SET i = RIGHT(pLoc_TempIdPegawai_i,6) + 1;
                SET pLoc_TempIdPegawai = CONCAT(p_JenisKelamin ,`fc_FormatNomor`(pLoc_KdJenisPegawai,3),fc_FormatNomor(i,6));
            END IF;


        INSERT INTO `tbl_data_pegawai`
            (
                `IdPegawai`,
                `NIK`,      
                `KdTitle`,
                `NamaLengkap`,
                `JenisKelamin`,
                `TempatLahir`,
                `Alamat`,
                `TglLahir`
            )
        VALUES  
            (
                pLoc_TempIdPegawai,
                p_NIK,
                pLoc_KdTitle,
                p_NamaLengkap,
                p_JenisKelamin,
                NULL,
                NULL,
                NULL
            );
        /*insert ke tabel tbl_data_current_pegawai */   
        INSERT INTO `tbl_data_current_pegawai`
            (
                `IdPegawai`,
                `KdJenisPegawai`,
                `KdJabatan`,
                `KdDivisi`,
                `KdAgama`,
                `KdPendidikan`,
                `StatusEnabled`
            )
        VALUES
            (
                pLoc_TempIdPegawai,
                pLoc_KdJenisPegawai,
                p_KdJabatan,
                p_KdDivisi,
                NULL,
                NULL,
                p_KdStatusAktif
            );


        SET OutputId = pLoc_TempIdPegawai;
--  else
        IF UPPER(p_Status)= 'A' THEN

            UPDATE `tbl_data_pegawai`
            SET
                `IdPegawai`=p_IdPegawai,        
                `KdTitle`=pLoc_KdTitle,
                `NamaLengkap`=p_NamaLengkap,
                `JenisKelamin`=p_JenisKelamin
            WHERE `IdPegawai`=p_IdPegawai AND `KdTitle`=pLoc_KdTitle;

            /* Update tbl_data_current_pegawai */
            UPDATE `tbl_data_current_pegawai`
            SET
                `IdPegawai`=p_IdPegawai,
                `KdJabatan`=p_KdJabatan,
                `KdDivisi`=p_KdDivisi,
                `StatusEnabled`=p_KdStatusAktif
            WHERE `IdPegawai`=p_IdPegawai;

        ELSE

            DELETE FROM `tbl_data_pegawai` WHERE `IdPegawai`=p_IdPegawai;
            DELETE FROM `tbl_data_current_pegawai` WHERE `IdPegawai`=p_IdPegawai;

            SET OutputId = p_IdPegawai;

        END IF;             
    END IF;
    END$$

DELIMITER ;

--==================================================================================

how clear this error? i am sorry because my english language not good.

thank you

Solution 1

When we specify the parameter as OUT or INOUT, the stored procedure should be able to operate on that parameter. So it expects the paramter to be a variable which the caller can use later. If we specify a VALUE, its not possible for the Stored Procedure to manipulate that value, thus it will throw an 1414 error.

We can pass values only for IN parameter of the Stored procedure.

So define a session variable and then send that variable as a parameter.

Solution 2

13.2.1 CALL Syntax

...

To get back a value from a procedure using an OUT or INOUT parameter, pass the parameter by means of a user variable, and then check the value of the variable after the procedure returns. (If you are calling the procedure from within another stored procedure or function, you can also pass a routine parameter or local routine variable as an IN or INOUT parameter.)

...

Try:

-- call `sp_MasterDataPegawai`('','0123555','neni','P','001','001',1,'',null)
call `sp_MasterDataPegawai`('','0123555','neni','P','001','001',1,'',@`_OutputId`);

Comments

[ExpectoPatronum]

Its something as SET @ROW_ID = 0; CALL SP_CUSTOM('IN_PARAM_1','IN_PARAM_2', @ROW_ID); SELECT @ROW_ID;

[Xenos]

Suppose I don't need the out-ed value, is there a "placeholder" I can pass to the procedure without requiring a session variable/local variable? like CALL sp_MasterDataPegawai(..., VOID_OR_SO) instead of CALL sp_MasterDataPegawai(..., @ignoredVariable)? I dislike making a session variable that I don't intend to use, and I cannot remove the OUT parameter since some other calling places need its value, and "making a 2nd procedure without the OUT parameter and calling the existing procedure with an ignored local variable" seems heavy too