MySQL How to get results after PREPARE and EXECUTE in Stored Procedure?
10,042
As stated here you need to include the variable assignment in the original statement declaration. So you statement would be something like:
SELECT COUNT(*) FROM train WHERE ?<=0 INTO _a
Then you you would execute it with:
EXECUTE stmt1 using _car;
And get the result with:
select _a;
Let me know if it works.
Author by
lostsheep
Updated on June 27, 2022Comments
-
lostsheep almost 2 years
My current code is :
DELIMITER \\ CREATE PROCEDURE sample (IN _car VARCHAR(15)) BEGIN DECLARE _a INTEGER; SET @s = CONCAT('SELECT COUNT(*) FROM train WHERE ', _car, '<=0;'); PREPARE stmt1 FROM @s; EXECUTE stmt1; DEALLOCATE PREPARE stmt1; END\\
But I wanted to capture the answer of the SELECT statement to my _a variable.
I tried changing my code to
SET @s = CONCAT('SELECT COUNT(*) INTO', _a,' FROM train WHERE ', _car, '<=0;');
But that didn't work.
Help, please?
SOLVED!
DELIMITER \\ CREATE PROCEDURE sample (IN _car VARCHAR(15)) BEGIN DECLARE _a INTEGER; SET @var = NULL; SET @s = CONCAT('SELECT COUNT(*) INTO @var FROM train WHERE ', _car, '<=0;'); PREPARE stmt1 FROM @s; EXECUTE stmt1; SELECT @var; DEALLOCATE PREPARE stmt1; END\\
:D