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
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.