CODEWITHSUNDEEP
mysqlsqlstored-procedures
lots_of_questions
lots_of_questions

Reputation: 1149

MySQL Stored Procedure variables

In MySQL I'm trying to write a Stored Procedure which does the following:

  1. Run a SELECT query which returns 1 row with 2 columns (so two pieces of information).
  2. Run an INSERT query which includes the two previous values returned and a few parameters which were passed into the stored procedure.

I was originally thinking I could store the two pieces of information from Step 1 into a variable, and then pass those into Step 2. After some reading though it seems that a variable in MySQL can only hold 1 piece of data. How can I get the 2 pieces of information from Step 1 into my INSERT statement in Step 2?

Thank you!

Upvotes: 1

Views: 1729

Answers (3)

Shiplu Mokaddim
Shiplu Mokaddim

Reputation: 57690

You can use INSERT INTO ... SELECT. Example,

INSERT INTO table1 
SELECT col1, 
       col2, 
       value1, 
       value2 
FROM   table2

Here you select col1, col2 from table2 and add 2 new custom values value1 and value2 to the result set. This new 4 columns get inserted in to the table1

Upvotes: 2

John Woo
John Woo

Reputation: 263943

create something like this:

CREATE PROCEDURE InsertThis(IN paramA varchar(50), IN paramB int, INT paramC varchar(50))
BEGIN
     INTO INTO tableB(fieldA, fieldB, fieldC, fieldD)
     SELECT paramA as fieldA, paramB as fieldB, fieldC, fieldD
     FROM tableA
     WHERE fieldC = paramC;
END

Upvotes: 0

Vadym S. Khondar
Vadym S. Khondar

Reputation: 1438

You can use SELECT ... INTO clause, which will select values from specified table to variables you define in stored proc.

Refer to manual: http://dev.mysql.com/doc/refman/5.0/en/select-into.html

Upvotes: 1

Related Questions