Procedure with where clause and parameters

Question

With big help of @lemon I did 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

ID	item_id	name	surname	addition	addition2	addition3	question1	question2	question3	amount
1	1	Gladys	Warner	hot-dog	pizza	-	mayo	chilli	-	25
2	2	Harrison	Croft	pizza	burger	hod-dog	chilli	mayo	-	25

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 TEXT;
      DECLARE post_title_param TEXT;
      DECLARE additional_option_name_param TEXT;
      DECLARE ask_user_param TEXT;
     
      SET is_payment_param = IF(isPayment IS NOT NULL, "AND is_payment IN (isPayment)","");
      SET post_title_param = IF(postTitle IS NOT NULL, " AND post_title IN (postTitle)","");
      SET additional_option_name_param = IF(optionName IS NOT NULL, " AND additional_option_name IN (optionName)","");
      SET ask_user_param = IF(askUser IS NOT NULL, " AND ask_user IN (askUser) ","");
  
   
SELECT CONCAT(" WHERE 1=1 ", is_payment_param, post_title_param, additional_option_name_param ,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)';   
    
   

SET @sql = CONCAT(@cte,
                  'SELECT user_id, name, surname,',
                  @sql,
                  ',SUM(additional_option_price) AS additional_option_price FROM cte',
                  @where,
                  '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

Before attempting to make a procedure, can you make an example of how your final **static** query should look like? This would be helpful to understand how it is different with respect to the static query generated here >> https://stackoverflow.com/questions/72510030/join-into-pivot/72510963#72510963. — lemon, Jun 15 '22 at 16:30

Akina · Answer 1 · 2022-06-15T12:41:02.043

1

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:

edited Jun 15 '22 at 12:41

answered Jun 15 '22 at 12:34

Akina

39,301
5
14
25

do you mean ```SET @is_payment_param = ... -- is_payment_param ``` @Akina – Hub Jun 15 '22 at 12:41
@Hub You may use ```SET `@is_payment_param` = ...``` - but I do not recommend. – Akina Jun 15 '22 at 12:41
In your code is ```SET @is_payment_param = ... -- is_payment ``` does the parameter name really change? – Hub Jun 15 '22 at 12:43
@Hub In **MY** code ??? This is a part of **YOUR** code posted in your question. The comments contains the explanation what type of object is used in each particular code place. You define one variable but use another one. – Akina Jun 15 '22 at 12:44
in your comment @Akina do you see what i mean ? – Hub Jun 15 '22 at 12:46
@Hub I recommend you to remove ALL `@` symbols, except the variable which is used for prepared statement (`@sql`). And also avoid using the variables names which are the same as columns names. Also declare all used variables which are not declared in the procedure definition. – Akina Jun 15 '22 at 12:48

score 1 · Answer 2 · answered Jun 15 '22 at 12:35

1

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.

answered Jun 15 '22 at 12:35

slaakso

8,331
2
16
27

*Procedure parameters do not use the @-prefix.* There is no parameter variables with prefix in shown code. These are variables with quoted names, and `@` is a part of name. If only there were no backticks .. but then an error message would be displayed. – Akina Jun 15 '22 at 12:36

Procedure with where clause and parameters

PS.

2 Answers2