CODEWITHSUNDEEP
phpmysqlcodeigniterstored-procedures
melonQheadQsheep
melonQheadQsheep

Reputation: 121

error code 1414 occurs in mysql when i call stored procedure with ouput parameter

I'm trying to call a stored procedure (SP) from my codeigniter code but one of the parameters I defined in the Mysql SP is a OUTPUT parameter which is giving me some issues when calling the SP. Does anyone know the correct way to call the Sp from the PHP code with a OUTPUT parameter involved. The code is below:

MySql:

DROP PROCEDURE IF EXISTS usp_check_user_exist;
DELIMITER $$
CREATE PROCEDURE usp_check_user_exist
( IN email VARCHAR(200),
  OUT result BIT(1)  )
BEGIN
SET result = EXISTS(SELECT 1 FROM tbl_users WHERE user_email = email
LIMIT 1);
SELECT result; 
END

Codeigniter/php: 

public function check_email($email) {

        $data = array(
                     'email' => $email,
                     '@result' => @result
                 );


        $sp = "CALL usp_check_user_exist (?,?)";
        $result = $this->db->query($sp, $data);

        if($result) {
            return TRUE;
        } else {
            return FALSE;
        }

    }

The error I got:

Database error:

Upvotes: 0

Views: 925

Answers (1)

Alex Mac
Alex Mac

Reputation: 2993

You have error in your stored procedure. Please check correct defination described below.

DELIMITER $$

DROP PROCEDURE `usp_check_user_exist`$$

CREATE PROCEDURE `usp_check_user_exist`(IN email VARCHAR(200))
BEGIN
    DECLARE result TINYINT DEFAULT 0;
    SET result = EXISTS(SELECT 1 FROM tbl_users WHERE user_email = email 1);
    SELECT result; 
END$$

DELIMITER ;

Also if you want to user your current Stored Procedure than while calling from PHP use statement like describe below.

call usp_check_user_exist('[email protected]',@out);

Let me know if it not works.

Upvotes: 1

Related Questions