MySQL CREATE PROCEDURE Syntax

Question

I have this table with lot of parameters :

idPumpParam|Type   |Version|A10A|A11A|A12A|A13A|A14A|...

1          |Pump1  |DL cons|1   |32  |45  |6   |67  |...

2          |Pump2  |DL var |2   |65  |8   |37  |43  |...

For search the parameter with my automation, I would like to create a procedure in MySQL Workbench 5.7:

CREATE DEFINER = `root`@`localhost` 
PROCEDURE `procReadParam`(IN Param VARCHAR(20), 
                          IN TypePpe VARCHAR(20), 
                          IN Version VARCHAR(20))
BEGIN
    SELECT Param FROM pumpparameters.pumpparam 
    WHERE Type1 LIKE TypePpe AND Version=Version AND Frequence=Freq;
END

The procedure is good, no error message.

I test the procedure in MySQL Workbench:

call pumpparameters.procReadParam(A12A, 'Pump1', 'DL cons')

There are this error message:

Error Code: 1054. Unknown column 'A12A' in 'field list'

Can you help me for this error message please?

JayCo741 · Accepted Answer · 2019-04-15 15:07:11Z

2

Have you tried calling the procedure with Param in single quotes? Like this: call pumpparameters.procReadParam('A12A', 'Pump1', 'DL cons')

answered Apr 15, 2019 at 15:07

JayCo741

6032 gold badges9 silver badges24 bronze badges

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

1 Comment

Alex Over a year ago

With ' ' the answer is : Param | A12A

Thomas G · Accepted Answer · 2019-04-16 07:00:53Z

1

If you want to use a column dynamically in a procedure, you can prepare and execute a SQL string statement

Something like this:

DELIMITER //
CREATE DEFINER = `root`@`localhost` 
PROCEDURE `procReadParam`(IN Param VARCHAR(20), 
                          IN TypePpe VARCHAR(20), 
                          IN Version VARCHAR(20))
BEGIN
  SET @sql = CONCAT('SELECT ', Param, ' FROM  pumpparameters.pumpparam WHERE Type1 = \'', TypePpe ,' AND Version =\'',Version,'\'');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END//
DELIMITER ;

Notes:

for your VARCHAR columns, you should surround their values with single quotes, and thus properly escape that in your CONCAT (with \')
I replaced the LIKE with = because a LIKE had no purpose there.
I removed the Freq part because you don't pass it as a parameter

So you have to make a few adaptations to make it working like you want

edited Apr 16, 2019 at 7:00

answered Apr 15, 2019 at 15:29

Thomas G

10.3k7 gold badges33 silver badges45 bronze badges

1 Comment

Alex Over a year ago

For the moment it's a test table, the real table will be very big. I need LIKE '%Pump1%' because instead of Pump1 there are many pump with the same parameters : Type | MN 0100B, MP 0100 B, MQ 0100 B... | NU 2000 N| BJ 0400 B, BG 1300 J.... . I need Freq because the same Pump can have a motor in 50 or 60 or 90Hz but with differents parameters

Collectives™ on Stack Overflow

MySQL CREATE PROCEDURE Syntax

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related