related to "IN parameter" in mysql

Question

when i GOOGLED about IN parameter i got this..

the value of an IN parameter is protected. It means that even if the value of the IN parameter is changed inside the procedure, its original value is retained after the procedure ends (like pass by value).

i didn't understand the context of above search result

i tried this code but the original value is changed from 1 to 2.

delimiter //
CREATE PROCEDURE MyProcedure(IN myParameter INT)
BEGIN
    -- Output the original value
    SELECT myParameter AS 'OriginalValue';
    
    -- Rest of the code
    
    -- Modify the parameter value (which should be avoided)
    SET myParameter = myParameter + 1;
    
    -- Output the modified value
    SELECT myParameter AS 'ModifiedValue';
END //
delimiter ;
call myprocedure(1);

score 2 · Answer 1 · answered Jun 24 '23 at 14:28

Try this:

set @myvar = 1;
call myprocedure(@myvar);
select @myvar;

If the parameter is declared as an IN parameter in your procedure, the original value 1 is preserved after calling the procedure.

If the parameter is declared as an INOUT parameter, the incremented value will become the new value of the variable in your session.

set @myvar = 1;
call myprocedure(@myvar);
...
select @myvar;
+--------+
| @myvar |
+--------+
|      2 |
+--------+

This is relevant only if you pass a variable as the argument. It's not allowed to pass a numeric literal to the procedure as an INOUT or OUT parameter.

call myprocedure(1)
ERROR 1414 (42000): OUT or INOUT argument 1 for routine test.MyProcedure is not a variable or NEW pseudo-variable in BEFORE trigger

score 1 · Answer 2 · answered Jun 24 '23 at 14:32

You essentially have a stored procedure and you successfully change the value of the variable inside the procedure, but that's only a copy of the original. If you have a variable that is being set to a value and then passed to the stored procedure, after the stored procedure ends, the variable you passed to it will remain unchanged even if you changed its copy inside the stored procedure.

Think of it like this:

you have a piece of paper
you copy that piece of paper
you change the copy
but the initial is not changed when you change the copy

Example:

delimiter //
CREATE PROCEDURE MyProcedure(IN myParameter INT)
BEGIN
    -- Output the original value
    SELECT myParameter AS 'OriginalValue';
    
    -- Rest of the code
    
    -- Modify the parameter value (which should be avoided)
    SET myParameter = myParameter + 1;
    
    -- Output the modified value
    SELECT myParameter AS 'ModifiedValue';
END //
delimiter ;
SET @myParameter = 1;
call myprocedure(@myParameter);
SELECT @myParameter as `NotReallyModified`;

The result of the last select is 1, which proves that the variable did not change even though it was passed to the stored procedure and its copy was changed:

related to "IN parameter" in mysql

2 Answers2