Using the input parameters from stored parameters as variables inside where clause MySQL

Question

I have a simple mysql table:

CREATE TABLE  `cont` (
  `ID` int(11) NOT NULL,
  `Meeting_id` int(11) DEFAULT NULL,
  `member_name` varchar(20) NOT NULL,
  `cont_prod` varchar(20) NOT NULL,
  `start_date` date NOT NULL,
  `type_of` varchar(100),
  `ord_qty` int(11) DEFAULT NULL
) ;

I am trying to dynamically transpose rows to columns following this example enter link description here

In side the SELECT STATEMENT when I use the input parameters inside WHERE clause it is giving me error column not found.
I tried to declare the variable as :

SET @v1 = c_prod; SET @v2 = s_date;

and teh in the where clause:

WHERE cont_prod = @v1 AND start_date = @v2

it is still not working

How can I define the input parameter variable inside the WHERE clause?

Any help would be highly apprecuited.

 BEGIN
         SET @sql = NULL;
    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'max(case when type_of = '',
          type_of,
          '' then ord_qty end) ',
          type_of
        )
      ) INTO @sql
    FROM
      cont
      where cont_prod = c_prod AND start_date = s_date;
    SET @sql = CONCAT('SELECT product_id,member_name,start_date,cont_prod, ', @sql, ' 
                      FROM cont WHERE cont_prod = c_prod AND start_date = s_date
                       GROUP BY member_name,cont_prod,start_date');
    
    PREPARE stmt FROM @sql;
    EXECUTE st......

'' should be '''

Barmar
– Barmar

2021-06-14 11:29:54 +00:00
Commented Jun 14, 2021 at 11:29 — Barmar
– Barmar, Commented Jun 14, 2021 at 11:29

Barmar · Accepted Answer · 2021-06-14 11:34:42Z

1

Use ? to specify parameters in the prepared statement, and pass the parameters on the EXECUTE line.

SET @sql = CONCAT('SELECT product_id,member_name,start_date,cont_prod, ', @sql, ' 
            FROM cont WHERE cont_prod = ? AND start_date = ?
            GROUP BY member_name,cont_prod,start_date');
PREPARE stmt FROM @sql;
EXECUTE stmt USING c_prod, s_date;

answered Jun 14, 2021 at 11:34

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Khalid Teli Over a year ago

thank you. Appreciate your help!! it was giving the error on Excute stmt line. Si have to declare variables an duse them lik: EXECUTE stmt USING '@v1', '@v2';

Collectives™ on Stack Overflow

Using the input parameters from stored parameters as variables inside where clause MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related