I have procedure with some params and i need to include them in WHERE clause. I stuck and don't know if problem is in my logic or just in syntax.

result of my procedure without params

parameters help you choose type of addition, question and a few others

DELIMITER $$

CREATE PROCEDURE `ReportAdditionals`(IN `isPayment` TINYINT(1), IN `postTitle` TEXT, IN `optionName` TEXT, IN `askUser` TEXT)

BEGIN
      DECLARE is_payment_param INT;
      SET is_payment_param = IF(isPayment IS NOT NULL OR isPayment = "", "is_payment in (isPayment)",0);
      
      DECLARE post_title_param TEXT;
      SET post_title_param = IF(postTitle IS NOT NULL OR postTitle = "", "post_title in (postTitle)",0);
      
      DECLARE additional_option_name_param TEXT;
      SET additional_option_name_param = IF(optionName IS NOT NULL OR optionName = "", "additional_option_name in (optionName)",0);
      
      DECLARE ask_user_param TEXT;
      SET ask_user_param = IF(askUser IS NOT NULL OR askUser = "", "ask_user in (askUser)",0);
   
SELECT CONCAT(" WHERE 1=1"," AND ",is_payment_param," AND ",post_title_param," AND ",additional_option_name_param," AND ",ask_user_param)into @where;


WITH cte AS(
    SELECT DISTINCT ROW_NUMBER() OVER(PARTITION BY event_items_id) AS idx
    FROM event_items_additional
)
SELECT GROUP_CONCAT(
           CONCAT('MAX(IF(rn_add = ', cte.idx, ', additional_option_name, NULL)) AS addition', cte.idx, ','
                  'MAX(IF(rn_qst = ', cte.idx, ', ask_user, NULL)) AS question', cte.idx
       )) INTO @sql
FROM cte;

SET @cte = 'WITH cte AS(
SELECT post_title, users.id AS user_id, name, surname, additional_option_name, ask_user, additional_option_price,
ROW_NUMBER() OVER(PARTITION BY name, surname ORDER BY IF(additional_option_name IS NULL, 1, 0), post_title) AS rn_add,
ROW_NUMBER() OVER(PARTITION BY name, surname ORDER BY IF(ask_user IS NULL, 1, 0), post_title) AS rn_qst 
FROM users 
LEFT JOIN event_items 
       ON users.id = event_items.id 
LEFT JOIN event_items_additional 
       ON users.id = event_items_additional.event_items_id 
LEFT JOIN event_items_ask_user  
       ON users.id = event_items_ask_user.event_items_id 
--
--IF(@is_payment IS NULL OR @is_payment = "", is_payment in (@is_payment),0) AND
--IF(@post_title IS NULL OR @post_title = "", post_title in (@post_title),0)
--
--OR
--
--   @where  
--
--OR
--
--Some different way 
   )';
    
   

SET @sql = CONCAT(@cte,
                  'SELECT user_id, name, surname,',
                  @sql,
                  ',SUM(additional_option_price) AS additional_option_price FROM cte GROUP BY user_id, name, surname'
);

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;
END$$
DELIMITER ;

PS.

I have asked this question once before but I did't explain it well

CodePudding user response：

Procedure parameters do not use the @-prefix. Similarily you better declare the local variables with declare instead of using user defined parameters.

For the SQL itself, avoid the dynamic SQL.

CodePudding user response：

CREATE PROCEDURE `ReportAdditionals`(
IN `@is_payment` TINYINT(1),   -- This is LOCAL VARIABLE with the name @is_payment

...

SET @is_payment_param = ...    -- This is USER DEFINED variable with then name is_payment

...

The same issue with another variables.

Study:

• User-Defined Variables
• Variables in Stored Programs