MTK MTK - 5 months ago 14
SQL Question

can't create stored procedure mysql maibe if else syntax

I try to make one store procedure like this but not work

DROP PROCEDURE IF EXISTS `my_test`;
CREATE PROCEDURE `my_test`(
IN my_in_var VARCHAR(255),
OUT my_out_var VARCHAR(255)
)
BEGIN
IF(in_var == 'my_in_value') THEN
SET my_out_var = 'my_out_value1';
ELSE
SET my_out_var = 'my_out_value2';
END IF;
END


I try to execute from php with PDO

$conn = new PDO("mysql:host=".$mysql_host.";dbname=".$mysql_bd, $mysql_user, $mysql_password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->exec($sql);


no errors but I can't see it on MySql server with


SHOW CREATE PROCEDURE my_test


So I tried to copy paste it in the SQL window of phpmyadmin and I got this sintax error:


1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '== 'my_in_value') THEN
SET my_out_var = 'my_out_value1'' at line 6


What is the correct syntax ? and why PDO not show the error ?

Any help are welcome thank's

Answer

Note, it has been pointed out to me that the below DELIMITER is not necessary in PHPMyAdmin which I don't use. So, DELIMITER is a client thing, such as needed by the likes of MySQL Workbench.

Stored Proc:

DROP PROCEDURE IF EXISTS `my_test`;
DELIMITER $$
CREATE PROCEDURE `my_test`(
  IN my_in_var VARCHAR(255),
  OUT my_out_var VARCHAR(255)
)
BEGIN
  IF (my_in_var = 'my_in_value') THEN
    SET my_out_var = 'my_out_value1';
  ELSE
    SET my_out_var = 'my_out_value2';
  END IF;
END$$
DELIMITER ;

Test:

set @outme='';
call my_test('lizard',@outme);
select @outme;
-- my_out_value2

set @outme='';
call my_test('my_in_value',@outme);
select @outme;
-- my_out_value1

So you need to figure out what your intention is with the above.

You had a syntax error with the double =. And you were perhaps having a typo in the in_var that did not exist.

PHPMyAdmin (that does not require DELIMITER so I am told):

DROP PROCEDURE IF EXISTS `my_test`;
CREATE PROCEDURE `my_test`(
  IN my_in_var VARCHAR(255),
  OUT my_out_var VARCHAR(255)
)
BEGIN
  IF (my_in_var = 'my_in_value') THEN
    SET my_out_var = 'my_out_value1';
  ELSE
    SET my_out_var = 'my_out_value2';
  END IF;
END