Concatenating a variable multiple times in string in MySQL

Question

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?

I can still see syntax errors. It is unclear what you are trying to do here ? — Madhur Bhaiya
– Madhur Bhaiya, Commented Nov 22, 2018 at 10:45
@MadhurBhaiya I am trying to concatenate a string into a variable, but am getting the syntax error. — DGMS89
– DGMS89, Commented Nov 22, 2018 at 10:46

Bernd Buffen · Accepted Answer · 2018-11-22 11:23:34Z

1

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]>

edited Nov 22, 2018 at 11:23

answered Nov 22, 2018 at 10:48

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DGMS89 Over a year ago

Thanks for the answer. Just tried it. If I try to set a value to floatvar ('test' for example) I get the same error as before.

Bernd Buffen Over a year ago

@DGMS89 - I have add a sample in my answer without any error

P.Salmon · Accepted Answer · 2018-11-22 11:00:38Z

set @floatvar := 'test1' ;

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

select @random_var2;

gives this

SELECT test1 AS Fields, COUNT(CASE WHEN test1 IS NULL THEN 1 END) AS NullCount;

Results in

ERROR 1054 (42S22): Unknown column 'test1' in 'field list'

If you do this

prepare sqlstmt = @random_var2;
execute sqlstmt;
deallocte prepare sq;stmt;

result

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= @random_var2' at line 1

Its best to check the prepared statement works before firing into dynamic sql because the error messages from dynamic sql may not be helpful.

Collectives™ on Stack Overflow

Concatenating a variable multiple times in string in MySQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related