I got 1064 error when running prepare statement with if condition

Question

DROP PROCEDURE IF EXISTS `test_proc`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `test_proc`(param_a int)
BEGIN
set @sqlstr_3=concat('if (1=',param_a,') then ');
set @sqlstr_3=concat(@sqlstr_3, 'select 1;else select 2;');
set @sqlstr_3=concat(@sqlstr_3, ' end if;');
select @sqlstr_3;
/**
prepare stmt_3 from @sqlstr_3;
execute stmt_3;
deallocate prepare stmt_3;
*/
if (1=param_a) then select 1;else select 2; end if;
END
;;
DELIMITER ;

When I call this procedure using:call test_proc(2) ,it works fine and returns two result:
result1: if (1=2) then select 1;else select 2; end if;
result2: 2

But when I using prepare statement.It got errors.

DROP PROCEDURE IF EXISTS `test_proc`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `test_proc`(param_a int)
BEGIN
set @sqlstr_3=concat('if (1=',param_a,') then ');
set @sqlstr_3=concat(@sqlstr_3, 'select 1;else select 2;');
set @sqlstr_3=concat(@sqlstr_3, ' end if;');
select @sqlstr_3;

prepare stmt_3 from @sqlstr_3;
execute stmt_3;
deallocate prepare stmt_3;

-- if (1=param_a) then select 1;else select 2; end if;
END
;;
DELIMITER ;

I call this procedure using:call test_proc(2) ,it got some errors:

[Err] 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 'if (1=2) then select 1;else select 2; end if' at line 1

Can you help me to solve this problem?

Answer 1

If you need to use dynamic SQL for compound statement then you must convert it into single statement. For example, your

if (1=param_a) then select 1;else select 2; end if;

may be converted to something near

select 1 WHERE (1=param_a)
UNION ALL
select 2 WHERE NOT (1=param_a);

---

Alternatively you may use separate conditional blocks with individual SQL statement creation (and maybe even PREPAREs):

IF 1=param_a THEN
    set @sqlstr_3='select 1;';
ELSE
    set @sqlstr_3='select 2;';
END IF;

PREPARE stmt_3 FROM @sqlstr_3;
EXECUTE stmt_3;
DROP PREPARE stmt_3;