R2D2 R2D2 - 3 months ago 10
MySQL Question

Using session variables in a stored procedure

I just started learning MySQL and creating stored procedures. This procedure works fine when I use local variables. When I try to use a session variable in my procedure I receive a syntax error. Can I not use session parameters in procedures and if not, where can I use them?

DELIMITER $$
CREATE PROCEDURE myfirst()
BEGIN
DECLARE @counter INT DEFAULT 1;
DECLARE @result VARCHAR(30);
WHILE (counter < (SELECT count(*) FROM animals))
DO
SET result= (SELECT name FROM animals WHERE id = counter);
SELECT result;
SET counter = counter + 1;
END while;

END$$
DELIMITER ;

Answer

You can use User Variables (session as you call them), but you cannot pass them with an @ sign as a stored proc parameter definition.

Also, one never DECLAREs User Variables with DECLARE.

So just use them. There are in your session. That is, if you must.

It would be preferable and more readable and supportable to merely create your stored procs with proper parameter names (without the @ sign). How you pass values in, whether with a User Variable reference or not, is up to you.

Regardless, stored proc parameters cannot appear in their signature with an @ sign. All the DECLARES must happen first after BEGIN (and only with Local Variables not with User Variables).

DROP PROCEDURE IF EXISTS myfirst;
DELIMITER $$
CREATE PROCEDURE myfirst()
BEGIN
    DECLARE LocalVar_Int INT DEFAULT 1; -- not used, just shown
    DECLARE LocalVar_Varchar VARCHAR(30); -- not used, just shown 

    -- all the DECLARES must come first after BEGIN
    -- and only for LOCAL VARS

    SET @count=1; -- this is a User Variable
    SET @result=''; -- this is a User Variable
    WHILE (@counter < (SELECT count(*) FROM animals))
    DO
    SET @result= (SELECT name FROM animals WHERE id = @counter);
    SELECT @result;
    SET @counter = @counter + 1;
    END while;

END$$
DELIMITER ;