stored procedure CONCAT() Error Code 1054, Unknown column

Question

thanks in advance for you help. you guys are awesome.

having trouble with CONCAT() inside a stored procedure. trying to create an INSERT INTO with variables.

ERROR CODE 1054, Unknown column 'Milk' in field list.

Milk is a value to be inserted, not a column.

Here is the CODE:

SET @s = CONCAT('insert into ',USR,' (Product, ProdId, ListName) VALUES (',food_name,',',PRODID,',',LISTID,')');

Answer 1

You need to quote the value of food_name:

SET @s = CONCAT('insert into ',USR,' (Product, ProdId, ListName) VALUES (\'',food_name,'\',',PRODID,',',LISTID,')');

As it is, MySQL will see the unquoted value Milk and assume it's a column name instead of a string literal.

If other columns in the list are also character types, you need to do the same for those columns as well. For example, making the assumption that ListName is also a character type, your procedure would be:

SET @s = CONCAT(
    'insert into ',
    USR,
    ' (Product, ProdId, ListName) VALUES (\'',
    food_name,
    '\',',
    PRODID,
    ',\'',
    LISTID,
    '\')'
);