I am trying to compile the following (see below) MySQL stored procedure and I keep getting Error Code 1064.

“You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 3 0.000 sec”

The purpose of the procedure is to cycle through menu_input table to gather parameters for building insert queries to populate data_ouput table.

The error line seems broken as it locates the error at the beginning of the script whereas that same beginning which is related to variables declaration compiles by himself. Also, if I’m not wrong, the error suggests using server syntax; I tried reviewing each statement of my code, comparing it to official syntax but found no deviation (also due to my limited experience).

The script follows:

CREATE PROCEDURE SodAnalysis()
BEGIN
    DECLARE strFieldName1 VARCHAR(100);
    DECLARE strFieldName2 VARCHAR(100);
    DECLARE strSQL VARCHAR(100);
    DECLARE rischio VARCHAR(100); -- Assuming these parameters need to be declared
    DECLARE categoria VARCHAR(100); -- and assigned values before executing @strSQL

    -- Cursor to iterate through menu_input records
    DECLARE menu_cursor CURSOR FOR
    SELECT 
        menu1, 
        menu2, 
        rischio, 
        categoria
    FROM menu_input;

    OPEN menu_cursor;
    FETCH NEXT FROM menu_cursor INTO strFieldName1, strFieldName2, rischio, categoria; -- Adjusted to fetch @rischio and @categoria as well

    WHILE @@FETCH_STATUS = 0 DO
        -- Construct the SQL query with parameters A and B
        SET strSQL = CONCAT('
            INSERT INTO data_output 
            SELECT 
                c.*, 
                d.funzione AS funzione2, 
                d.processo AS processo2 
            FROM (
                SELECT 
                    a.*, 
                    b.funzione AS funzione1, 
                    b.processo AS processo1 
                FROM (
                    SELECT 
                        t1.id AS id1, 
                        t2.id AS id2, 
                        t1.utente_cod, 
                        t1.utente_des, 
                        t1.societa AS societa1, 
                        t1.societa_des AS societa_des1, 
                        t2.societa AS societa2, 
                        t2.societa_des AS societa_des2, 
                        t1.ruolo_cod AS ruolo_cod1, 
                        t1.ruolo_des AS ruolo_des1, 
                        t2.ruolo_cod AS ruolo_cod2, 
                        t2.ruolo_des AS ruolo_des2, 
                        t1.modulo AS modulo1, 
                        t2.modulo AS modulo2, 
                        t1.modulo_des AS modulo_des1, 
                        t2.modulo_des AS modulo_des2, 
                        t1.menu_funz AS menu_funz1, 
                        t2.menu_funz AS menu_funz2, 
                        t1.progr_cod AS progr_cod1, 
                        t2.progr_cod AS progr_cod2, 
                        @rischio AS rischio, 
                        @categoria AS categoria 
                    FROM 
                        data_input t1
                    INNER JOIN 
                        data_input t2 
                    ON 
                        t1.utente_cod = t2.utente_cod 
                    WHERE 
                        t1.utente_cod IN (
                            SELECT utente_cod 
                            FROM data_input 
                            WHERE menu_funz = @strFieldName1
                        ) 
                        AND t1.utente_cod IN (
                            SELECT utente_cod 
                            FROM data_input 
                            WHERE menu_funz = @strFieldName2
                        ) 
                        AND t1.menu_funz = @strFieldName1 
                        AND t2.menu_funz = @strFieldName2
                ) a 
                LEFT JOIN 
                    transcodifica_input b 
                ON 
                    a.menu_funz1 = b.MENU
            ) c 
            LEFT JOIN 
                transcodifica_input d 
            ON 
                c.menu_funz2 = d.MENU;
        ');

        -- Execute the constructed SQL query
        PREPARE stmt from StrSQL;
        SET @rischio = rischio;
        SET @categoria = categoria;
        SET @strFieldName1 = strFieldName1;
        SET @strFieldName2 = strFieldName2;
        EXECUTE stmt USING @rischio, @categoria, @strFieldName1, @strFieldName2;
        deallocate prepare stmt;

        -- Move to the next record
        FETCH NEXT FROM menu_cursor INTO strFieldName1, strFieldName2, rischio, categoria; -- Adjusted to fetch @rischio and @categoria as well
    END WHILE;

    -- Close and deallocate the cursor
    CLOSE menu_cursor;
    DEALLOCATE menu_cursor;
END;
GO

I wish you the best hoping anyone could give a hand, Best Regards, David

I tried reviewing each statement of my code, comparing it to official syntax but found no deviation (also due to my limited experience)