CODEWITHSUNDEEP
mysqlvariablesdynamic
SamanthaAlexandria
SamanthaAlexandria

Reputation: 225

Issue with complex mysql query using prepared statement

The following procedure does not execute. int_id is a user-defined input

My goal is to use STR_TABLE_NAME as a dynamic variable for a table. NB: the query works however, if i replace it by the name of my target table and remove "SET @prep_stmt =".

BEGIN

DECLARE STR_TABLE_NAME VARCHAR(100) DEFAULT NULL;
SELECT `table_source` INTO STR_TABLE_NAME FROM `list_repository` WHERE 
id=`int_id` LIMIT 1;

DROP TABLE IF EXISTS `loyaltytry`;
SET @prep_stmt = CREATE TABLE `loyaltytry` as (SELECT Months as month, 
Number_of_New_Customers as `new_customers` , `Number_of_Repeat_Customers`  
as `repeat_customers`
from
(SELECT  monthname(Months) as Months, month(Months)  as `Month_number`,
sum(CASE WHEN REP_COUNT ='no' then cnts end) as `Number_of_New_Customers`,
sum(CASE WHEN REP_COUNT = 'yes' then cnts end) as 
`Number_of_Repeat_Customers`
from (
SELECT months,REP_COUNT,count(*) as cnts
from (
    SELECT (date_commande_client) as Months , numero,
    CASE WHEN cnt > 1 THEN 'yes'
    ELSE 'no'
    END AS REP_COUNT
    from ( 
    SELECT COUNT(*) as cnt, date_commande_client, numero
    FROM  STR_TABLE_NAME           
        WHERE YEAR(date_commande_client)=2017
        AND intitule IN (SELECT showroom_name FROM `showrooms` WHERE 
id_region=`int_id`)
       group by date_commande_client , numero) as tmp) as final
    GROUP BY Months,REP_COUNT ) as tmp1
GROUP BY monthname(Months),month(Months)
ORDER BY Month_number) as finalll
);

PREPARE stmt FROM @prep_stmt; 
EXECUTE stmt; 
DEALLOCATE PREPARE stmt;

END

Upvotes: 0

Views: 218

Answers (1)

Dark Knight
Dark Knight

Reputation: 6541

  • To include variables in Prepared Statement, i used string concatenation. Your query will give error because STR_TABLE_NAME will be treated as table name and won't find any.
  • Remove (`) backtick from input parameter.
BEGIN

    DECLARE STR_TABLE_NAME VARCHAR(100) DEFAULT NULL;
    SELECT table_source INTO STR_TABLE_NAME FROM list_repository WHERE id = int_id LIMIT 1;

    DROP TABLE IF EXISTS `loyaltytry`;

    SET @prep_stmt = CONCAT('CREATE TABLE `loyaltytry` AS (
                                        SELECT 
                                            Months AS MONTH, 
                                            Number_of_New_Customers AS `new_customers`, 
                                            `Number_of_Repeat_Customers` AS `repeat_customers`
                                        FROM (
                                            SELECT 
                                                MONTHNAME(Months) AS Months, 
                                                MONTH(Months) AS `Month_number`, 
                                                SUM(CASE WHEN REP_COUNT = "no" THEN cnts END) AS `Number_of_New_Customers`, 
                                                SUM(CASE WHEN REP_COUNT = "yes" THEN cnts END) AS `Number_of_Repeat_Customers`
                                            FROM (
                                                SELECT 
                                                    months,
                                                    REP_COUNT, 
                                                    COUNT(*) AS cnts
                                                FROM (
                                                    SELECT 
                                                        (date_commande_client) AS Months, 
                                                        numero, 
                                                        CASE WHEN cnt > 1 THEN "yes" ELSE "no" END AS REP_COUNT
                                                    FROM (
                                                        SELECT 
                                                            COUNT(*) AS cnt, 
                                                            date_commande_client, 
                                                            numero
                                                        FROM ',  STR_TABLE_NAME , 
                                                        ' WHERE YEAR(date_commande_client) = 2017 
                                                            AND intitule IN (
                                                                SELECT 
                                                                    showroom_name
                                                                FROM `showrooms`
                                                                WHERE id_region= ', int_id , ' 
                                                            )
                                                        GROUP BY date_commande_client, numero
                                                    ) AS tmp
                                                ) AS final
                                                GROUP BY Months, REP_COUNT
                                            ) AS tmp1
                                            GROUP BY MONTHNAME(Months), MONTH(Months)
                                            ORDER BY Month_number) AS finalll
                                        )');

    PREPARE stmt FROM @prep_stmt; 
    EXECUTE stmt; 
    DEALLOCATE PREPARE stmt;

END

Upvotes: 1

Related Questions