Mysql stored procedure to insert values ​​from select statement and parameters

I am writing a stored procedure to select multiple values ​​from a table and then insert each value on a new row into another table along with two parameters that are passed to the function.

All the documentation I can find shows how to insert selected results rather than insert selected results along with parameters, this is the syntax I want to know.

function (a, b)

select c from table
 for each result, 
    insert (a,b,c) into newtable (a,b,c)


In the above pseudocode, the value of c is retrieved from the select statement, but the values ​​for a and b are both stored procedure arguments. The select statement can return multiple values.

So let's say in a table:

id c
1 apple
2 orange
3 pears

Then I call the stored procedure:

procedure(recipe, book)


then we will see in the new table:

id abc
recipe book apple 2 recipe book orange
3 recipe book pear


source to share

1 answer

Ok, I rewrote it as a procedure so that you understand that a

and b

are not values ​​from a table table


CREATE PROCEDURE procedure(IN paramA VARCHAR(50), IN paramB VARCHAR(50)) 
        INSERT INTO newtable
        SELECT paramA, paramB, c
        FROM table


So, as in your example, you then call it like this:

CALL procedure('recipe','book');




All Articles