MySql Stored procedure.... error in group_concat

I have following query that works in phpMyAdmin

SELECT CONCAT("'",GROUP_CONCAT( fence_id SEPARATOR "','" ),"'") AS fence_ids 
FROM asset_fence af 
INNER JOIN assets a ON a.vehicle_id = af.vehicle_id 
WHERE a.client_id=1

But the same query gives me error in stored procedure

ERROR IS:

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 '@fence_ids = SELECT CONCAT("'",GROUP_CONCAT( fence_id SEPARATOR "','" ),"'") AS ' at line 8

STORED PROCEDURE IS

DELIMITER $$

CREATE PROCEDURE `prcDeleteClient`(IN `f_client_id` INT, OUT AROWS INT)
BEGIN


    START TRANSACTION;


        @fence_ids = SELECT CONCAT("'",GROUP_CONCAT( fence_id SEPARATOR "','" ),"'") AS fence_ids FROM asset_fence af INNER JOIN assets a ON a.vehicle_id = af.vehicle_id WHERE a.client_id=f_client_id


        DELETE
            asset_fence,
            geo_fence

        FROM 
            geo_fence gf INNER JOIN asset_fence af ON gf.fence_id = af.fence_id

        WHERE
            af.fence_id IN (@fence_ids)



        DELETE
            client,
            assets,
            asset_movement

        FROM 
            asset_movement am INNER JOIN assets a ON am.vehicle_id = a.vehicle_id
            assets a INNER JOIN client c ON a.client_id = c.client_id

        WHERE
            c.client_id=f_client_id


        SET @AROWS = ROW_COUNT();
        SELECT @AROWS as AROWS;

    COMMIT;
END $$

DELIMITER ;

Updated procedure is

DELIMITER $$

CREATE PROCEDURE `prcDeleteClient`(IN `f_client_id` INT, OUT AROWS INT)
BEGIN


    START TRANSACTION;


        DELETE
            af,
            gf
        FROM 
            geo_fence gf

            INNER JOIN asset_fence af ON gf.fence_id = af.fence_id
            INNER JOIN assets a ON a.vehicle_id = af.vehicle_id

        WHERE
            a.client_id=f_client_id;



        DELETE
            c,
            a,
            am

        FROM 
            asset_movement am INNER JOIN assets a ON am.vehicle_id = a.vehicle_id
            assets a INNER JOIN clients c ON a.client_id = c.client_id

        WHERE
            c.client_id=f_client_id;


        SELECT ROW_COUNT() AS AROWS;

    COMMIT;
END $$

DELIMITER ;

Error is: Unknown table 'c' in MULTI DELETE

What is the mistake and how can I rectify it?

Best Regards

Solution

First, your syntax for assigning variables is wrong, it needs the SET command before the variable name.

Second, if you want to use the result of a SELECT query as a value, you have to put parentheses around it:

SET @fence_ids = (SELECT ...);

Third, when you use:

WHERE af.fence_id IN (@fence_ids)

it will treat @fence_ids as a single ID, not a list of IDs. So this is equivalent to:

WHERE af.fence_id = @fence_ids

If you want to search for something in a comma-separated list, you need to use FIND_IN_SET:

WHERE FIND_IN_SET(af.fence_id, @fence_ids)

You also shouldn't add quotes around the values in your GROUP_CONCAT().

But you shouldn't use GROUP_CONCAT for this in the first place, you should just join with the query that returns all the IDs you want.

   DELETE
        af,
        gf
    FROM 
        geo_fence gf 
    INNER JOIN asset_fence af ON gf.fence_id = af.fence_id
    INNER JOIN assets a ON a.vehicle_id = af.vehicle_id 
    WHERE a.client_id=f_client_id;

You don't need to do this in two statements:

SET @AROWS = ROW_COUNT();
SELECT @AROWS as AROWS;

You can just do:

SELECT ROW_COUNT() AS AROWS;