Search code examples
mysqlvariablesstored-proceduresdeclare

MySQL Stored Procedure Variables - When to Use Which

I am new to MySQL. When do we use declared variable? When to use undeclared variable (@varTest).

I am wondering about the best practice. Thank you.

Solution

  • You should always create procedural variables with DECLARE to maintain proper scope. Session variables declared outside the function can be changed inside the function, and vice-versa.

    DROP PROCEDURE IF EXISTS foo;
DELIMITER $

CREATE PROCEDURE foo()
    BEGIN
        DECLARE foo INT;
        SET foo = 123;
        SET @foo = 456;
        SELECT foo, @foo;
    END$

DELIMITER ;

SET @foo = "BAR";
CALL foo();
SELECT @foo;