3

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

lostsheep
  • 39
  • 1
  • 1
  • 6

1 Answers1

2

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.

Community
  • 1
  • 1
james_bond
  • 6,778
  • 3
  • 28
  • 34
  • It didn't work: Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '_car' at line 7 – lostsheep Aug 08 '11 at 20:19