MySQL assign query value into variable

Question

I made procedure listed below:

delimiter // 
CREATE PROCEDURE processPayment(in id int(11), status int(11)) 
BEGIN 
declare msg_a text DEFAULT "Payment was marked as sent";
declare msg_d text DEFAULT "Payment was marked as denied and user balance was updated";
declare msg_w text DEFAULT "Wrong payment status";
declare msg_u text DEFAULT "Wrong payment ID";
declare amount varchar(255) DEFAULT 0;
amount = "SELECT amount FROM payments WHERE id = id";
user_id = "SELECT userid FROM payments WHERE id = id";

IF(SELECT COUNT(*) FROM payments WHERE id = id AND status = status) = 0
THEN
SELECT msg_u;
END IF;

IF(status = 0)
THEN
SELECT msg_w;
END IF;

IF(status = 1)
THEN
UPDATE payments SET status = status WHERE id = id;
SELECT msg_a;
END IF;

IF(status = -1)
THEN
UPDATE payments SET status = status WHERE id = id;
UPDATE balances SET actual = actual+amount WHERE user_id = user;
SELECT msg_d;
END IF;

END

But when i try to add it into database i get error: Something is wrong in your syntax near '= "SELECT amount FROM payments WHERE id = id"; user_id = "SELECT userid FROM pa' line 8 I need to assign query value into variable. How can i do this? Thanks in advance for any help.

Izuka · Accepted Answer · 2016-02-03 21:14:35Z

2

You have two ways in order to achieve this. First with SET :

SET amount  = (SELECT amount FROM payments WHERE id = id);
SET user_id = (SELECT userid FROM payments WHERE id = id);

Or with the SELECT INTO syntax :

SELECT amount INTO amount FROM payments WHERE id = id;
SELECT userid INTO user_id FROM payments WHERE id = id;

You can even achieve it in one line with this second method :

SELECT amount, userid INTO amount, user_id FROM payments WHERE id = id;

answered Feb 3, 2016 at 21:14

Izuka

2,5925 gold badges33 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

MySQL assign query value into variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related