Search code examples
mysqlconcatenationheidisql

Concatenating a variable multiple times in string in MySQL

Scenario: I am trying to set a string to a variable and then pass it to a prepared statement. I am using the "concat" function to create the string that will be passed.

Query:

set @floatvar := 'test1'    
set @random_var2 := concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount');

Obs: My query has more parts to be included in the string, but even with only this I am already having trouble, so I doing it step by step.

Issue: When I try to run this, I get a syntax error (SQL Error 1064) on line 2. I have no idea why this is happening.

Question: What is causing this issue, and how could it be fixed?

Solution

  • There is no SELECT .. INTO .. like this:

    SELECT 
 concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount')
INTO @myquery;

    Sample

    MariaDB [test]> SET @floatvar := 'test1'    ;
Query OK, 0 rows affected (0.002 sec)

MariaDB [test]> SELECT
    ->  concat('SELECT ', @floatvar, ' AS Fields, COUNT(CASE WHEN ', @floatvar, ' IS NULL THEN 1 END) AS NullCount')
    -> INTO @myquery;
Query OK, 1 row affected (0.000 sec)

MariaDB [test]>
MariaDB [test]> SELECT @myquery;
+--------------------------------------------------------------------------------+
| @myquery                                                                       |
+--------------------------------------------------------------------------------+
| SELECT test1 AS Fields, COUNT(CASE WHEN test1 IS NULL THEN 1 END) AS NullCount |
+--------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [test]>