Comments - Out Parameters in PREPARE
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
I want a parent stored procedure to iterate over a set of tables via a cursor or a while loop, and call a particular child stored procedure once per each table via the parent procedure call.
The prepared statement would be used to send each table name as an input parameter to the child stored procedure. Say, for example, if I want to programmatically create views for hundreds of tables at a time.
I would like the child stored procedure to emit status via an output parameter. Each output parameter returned is collected and logged by the parent stored procedure call.
Thus, inside the parent loop, we would have something like this:
SET @proc_call := CONCAT( "CALL mydb.create_table_view(‘”, @curr_table, "', @out_var);" ); START TRANSACTION; PREPARE stmt FROM @proc_call; EXECUTE stmt ; DEALLOCATE PREPARE stmt; COMMIT; INSERT mydb.view_creation_log(view_log_statement) VALUES (@out_var);
Perhaps such a use case might justify revisiting the feasibility of output parameters in prepared statements.