How to solve "OUT or INOUT argument is not a variable or NEW pseudo-variable in BEFORE trigger" in Stored Procedure

Question

I'm trying to extract value from output parameter in SQL procedure but I kept getting this exception

OUT or INOUT argument 2 for routine insertItem is not a variable or NEW pseudo-variable in BEFORE trigger

So this is my MySQL procedure

DELIMITER //
CREATE PROCEDURE insertItem(
    in $description varchar(200),
    out $outItemID int
)
BEGIN
    insert into item(
        description
    )
    values(
        $description
    );
    
    set $outItemID = last_insert_id();
    select $outItemID;
END //
DELIMITER ;

the stored procedure works fine when it execute in workbench

call insertItem('any description', @outItemID);

but when it comes to my C# backend service

using (connection){
 connection.Open();
 query = "call insertItem(@description, @outItemID);";
 command = new MySqlCommand(query, connection);
 AddParameterValue("@description", request.Item.Description);
 command.Parameters.AddWithValue("@outItemID", MySqlDbType.Int32);
 command.Parameters["@outItemID"].Direction = System.Data.ParameterDirection.Output;
 command.ExecuteNonQuery();

 ...//extract value from output parameter
}

the MySqlException was caught.

P.S. AddParameterValue functions work completely fine

`AddWithValue("@outItemID", MySqlDbType.Int32)` will add a parameter with *value* `MySqlDbType.Int32`, not with *type* `MySqlDbType.Int32` as you probably intended — Klaus Gütter, Feb 24 '22 at 08:33
@KlausGütter do you mean that I need to change ```AddWithValue``` to ```command.Parameters.Add(new MySqlParameter("@outItemID", MySqlDbType.Int32))```? If yes, I had tried that too, and it still getting the same error — jdczw, Feb 24 '22 at 08:44
@Akina thanks for pointing out, because I need to shorten the code, and I missed out to delete the comma — jdczw, Feb 24 '22 at 08:51
Your SQL code is stored procedure, and it is called as stored procedure, directly. Why the error message claims that it is used in a trigger? — Akina, Feb 24 '22 at 09:01
@Akina hmmm, good question, I don't think I'm able to answer your question. But here is a [link](https://stackoverflow.com/questions/9751707/mysql-not-a-variable-or-new-pseudo-variable-message) that mentioning the same exception — jdczw, Feb 24 '22 at 09:11
Oh, I see, it seems! You try to provide a value, not MySQL user-defined variable, to the CALL query! — Akina, Feb 24 '22 at 09:15
Enable General Log on MySQL temporarily. Execute your code. Then look in the log what SQL text is sent to MySQL from your program. — Akina, Feb 24 '22 at 09:36

Akina · Answer 1 · 2022-02-24T18:12:14.210

When you use CALL stored_procedure from the program it is executed as common SELECT query. Each SELECT in the SP body creates a rowset which then can be accessed as a rowset produced by SELECT query. If SP contains a lot of SELECT statements then the array/collection of separate rowsets will be returned (of course if access library supports this), and you must iterate them.

So your SP may:

contain only IN parameters;
use SELECT for to create output rowset with OUT values which then should be retrieved in the program like the output of single SELECT.

CREATE PROCEDURE insertItem( IN description varchar(200) )
BEGIN
    INSERT INTO item (description) VALUES (description);
    SELECT last_insert_id();
END

If you want to retrieve the result of SP via OUT/INOUT parameter then you may:

Create a batch which calls SP and then SELECTs the values returned via the parameters. Like

query = "CALL insertItem(@description, @outItemID); SELECT @outItemID;";

@description will be attached as a parameter, and it will be replaced with the value provided in your program code. @outItemID will not be replaced and will be provided to MySQL as-is. I.e. your program will send the SQL code which looks like CALL insertItem('Some description', @outItemID); SELECT @outItemID;. MySQL treates @outItemID as user-defined variable name. The output value will be assigned into it, and the next SELECT will output the value assigned into. Of course, the query again returns the rowset, and ExecuteNonQuery is not correct.

Maybe there exists the construction in your access library which allows to link the program's variable to OUT/INOUT parameter... but I doubt. This needs your library to create MySQL UDV which will be used for output value retrieving. The library won't create the name for such variable itself, because it may interfere with existing UDV which will result in its value damage. Maybe the library needs you to provide the name of such variable...

How to solve "OUT or INOUT argument is not a variable or NEW pseudo-variable in BEFORE trigger" in Stored Procedure

1 Answers1