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
1
recipe book apple 2 recipe book orange
3 recipe book pear
source to share
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))
BEGIN
INSERT INTO newtable
SELECT paramA, paramB, c
FROM table
END;
So, as in your example, you then call it like this:
CALL procedure('recipe','book');
source to share