MySQL Stored Procedure variables

Question

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

Run a SELECT query which returns 1 row with 2 columns (so two pieces of information).
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!

Look here:stackoverflow.com/questions/2450529/…

giorashc
– giorashc

2012-03-25 14:05:26 +00:00
Commented Mar 25, 2012 at 14:05 — giorashc
– giorashc, Commented Mar 25, 2012 at 14:05

Shiplu Mokaddim · Accepted Answer · 2012-03-25 14:12:50Z

2

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

edited Mar 25, 2012 at 14:12

answered Mar 25, 2012 at 14:01

Shiplu Mokaddim

57.5k20 gold badges147 silver badges193 bronze badges

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

Comments

Vadym S. Khondar · Accepted Answer · 2012-03-25 14:03:51Z

1

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

answered Mar 25, 2012 at 14:03

Vadym S. Khondar

1,4482 gold badges12 silver badges19 bronze badges

Comments

John Woo · Accepted Answer · 2012-03-25 14:12:22Z

0

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

answered Mar 25, 2012 at 14:12

John Woo

265k70 gold badges509 silver badges500 bronze badges

2 Comments

Shiplu Mokaddim Over a year ago

Where condition is not needed here

John Woo Over a year ago

It's only a sample bro. aside from using LIMIT what else can OP use to display 2 rows?

Collectives™ on Stack Overflow

MySQL Stored Procedure variables

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related