Failed to get dynamic SQL execution results Mysql-CodePudding

 SET m=CONCAT (N 'SELECT COUNT (*) INTO @ columnExistsCache FROM information_schema. The columns WHERE UPPER (TABLE_SCHEMA=UPPER (tableSchema', ') AND UPPER (TABLE_NAME)=UPPER (' ', tabName, N ') AND UPPER (COLUMN_NAME)=UPPER (columnName ', '); '); 
The SET @ sqlStr=m; 
PREPARE STMT FROM @ SQLSTR; 
The EXECUTE STMT. 
DEALLOCATE PREPARE STMT. 
The SET columnExists=@ columnExistsCache;

Judgment by dynamic SQL column exists, but unable to get to select into @ para way return value, for a great god,

CodePudding user response:

Found the reason, the dynamic parameter, the parameters before and after the need to join escaped single quotes,

CodePudding user response:

No problem!

 set @ a=null; Prepare st from the 'select count (*) into @ a from information_schema. The columns'; The execute st. Deallocate prepare st. Select @ a;

Mysql> The set @ a=null; Prepare st from the 'select count (*) into @ a from information_schema. The columns'; The execute st. Deallocate prepare st. Select @ a;
Query OK, 0 rows affected (0.00 SEC)

Query OK, 0 rows affected (0.00 SEC)
The Statement prepared

Query OK, 1 row affected, 2 warnings (0.04 SEC)

Query OK, 0 rows affected (0.00 SEC)

+ -- -- -- -- -- - +
| @ a |
+ -- -- -- -- -- - +
3532 | |
+ -- -- -- -- -- - +
1 row in the set (0.00 SEC)