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?
CREATE PROCEDURE myfirst()
DECLARE @counter INT DEFAULT 1;
DECLARE @result VARCHAR(30);
WHILE (counter < (SELECT count(*) FROM animals))
SET result= (SELECT name FROM animals WHERE id = counter);
SET counter = counter + 1;
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
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 ;