execute expression stored in a column

Question

I need some help about executing expression(bitwise) stored in a column of a table.
Input :

ID  | expression   
----|-------------
  1 | 1&0         
  2 | (1&1)|(0&1)

Desired Output :

ID  | expression  | value  
----|-------------|-------  
  1 | 1&0         |  0 
  2 | (1&1)|(0&1) |  1

I am trying something like below but it is not executing the expression.

PREPARE stmt from 'select ?  into @outvar';  
set @invar = '1&0';  
execute stmt using @invar;  
select @outvar;

The output of above is 1&0 but the desired output is 0.

Actually I want to store the output in a variable as framed in my above pseudo code.

Getting syntax error: check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONCAT('SELECT', @invar)' at line 1 — Ankur, Jun 22 '18 at 11:09
Possible duplicate of [How To have Dynamic SQL in MySQL Stored Procedure](https://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure) — Michał Turczyn, Jun 22 '18 at 11:52

score 0 · Answer 1 · answered Jun 22 '18 at 11:55

Using prepared statements, what are sent to server in the second round are treated literally. They can't be part of SQL. You shouldn't use placeholders for this:

SET @invar = '(1&1)&(0&1)'; 
SET @s = CONCAT('SELECT ', @invar, ' INTO @outvar');

PREPARE stmt FROM @s;
EXECUTE stmt;  
SELECT @outvar;

score 0 · Accepted Answer · answered Jun 22 '18 at 12:03

I have built you a procedure to do this

DROP TABLE IF EXISTS  test10;
DROP PROCEDURE IF EXISTS `sp_test10`;


create table test10 (ID int,  expression VARCHAR(20));


insert into test10 values ( 1 , '1&0');
insert into test10 values ( 2 , '(1&1)|(0&1)');


 CREATE PROCEDURE sp_test10 (IN IN_ID int, OUT OUT_VAL INT) 
 BEGIN 


     SELECT expression INTO @exp FROM test10 WHERE ID=IN_ID;

     SET @q = CONCAT('SELECT ',@exp,' INTO @exp '); 

     PREPARE stmt1 FROM @q; 
     EXECUTE stmt1; 

     DEALLOCATE PREPARE stmt1; 

     SET OUT_VAL = @exp;

 END\\ 
; 


call sp_test10(2,@result);
select @result;

Results :

call sp_test10(1,@result) returns 0
call sp_test10(2,@result) returns 1

http://rextester.com/live/SMLB31207

This solution really helped me as I was looking for stored procedure only.Thanks for your help. — Ankur, Jun 22 '18 at 14:37

execute expression stored in a column

2 Answers2