Search code examples
mysqlsqlstringdynamiccursor

MySQL adding string to itself

I'm trying to add string to itself in MySQL in cursor, i need that for my dynamic SQL query.

I have my string set before cursor

ESQL3 = "FIRST PART OF QUERY ";

Now in cursor i want to add the rest of the query, im using CONCAT() but i feel like its not doing the job.

SET @ESQL3 = CONCAT(ESQL3, aggregate_function, "(", table_name, ")" as ", table_name, " , ");

The result is declared first part of query + last call of that function. I've searched a lot for an answer.

Thank you

Solution

  • Looks like you want to reference the user defined variable, @ESQL3 as an argument in the CONCAT function, rather than the stored program variable ESQL3.

    Change this:

    SET @ESQL3 = CONCAT(ESQL3, ...

    to this:

    SET @ESQL3 = CONCAT(@ESQL3, ...
                    ^

    (MySQL stored program variables and MySQL user-defined variables are two different things. That is, ESQL3 and @ESQL3 are not the same variable. They are two fundamentally different variables.

    EDIT

    Also, the rest of the CONCAT arguments look kind of funky. These look okay:

    , aggregate_function
, "("
, table_name

    But this doesn't look right:

    , ")" as ", table_name, " , ");

    I don't think the "as" keyword is allowed in CONCAT, and that's not going to be seen as a string literal.

    Maybe you mean to include "as" as part of the string value, like this: 

    , ") as "
, table_name
, " , "

    FOLLOWUP

    You need to initialize @ESQL3. That's not happening in the code you posted. The scope of the user-defined variable is the session, the value of that variable persists across statements. The next time it's referenced in the session, it's going to have whatever value was last assigned to it.

    And when you initialize it, leave off the trailing comma. Add the comma when you append the next expression to the SELECT list.

    SET @ESQL3 = "CREATE TABLE Obroty AS SELECT Towar";
    ^                                            ^

    Note that we need to initialize the user-defined variable which is referenced later.

    It matters not one whit what the stored procedure variable ESQL3 is set to. That has no relationship to the user-defined variable @ESQL3.

    Inside the loop, when you are appending to @ESQL3, include the comma literal BEFORE the expression, rather than after it. Like this:

    SET @ESQL3 = CONCAT(@ESQL3, ", ", funkcja, "(", miech, ") as ", miech );
                            ^^^^

    So, entering the loop, @ESQL3 is going to have the value

    CREATE TABLE Obroty AS SELECT Towar
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

    After the first trip through read_loop, it will have a value like

    CREATE TABLE Obroty AS SELECT Towar, SUM(foo) AS foo
                                   ^^^^^^^^^^^^^^^^^

    The next time through the loop, it will have a value like

    CREATE TABLE Obroty AS SELECT Towar, SUM(foo) AS foo, SUM(bar) AS bar
                                                    ^^^^^^^^^^^^^^^^^

    When the loop is exited, you'll be ready to append "FROM whatever. (Make sure you have the space before the FROM.)