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 was trying to generate standard code for views over hundreds of tables by using one procedure to build the CREATE VIEW statement for an input table name, which then returns the CREATE VIEW statement is via output parameter.
A second stored procedure would build a list of the tables that require views, then loop over those table names, calling the first procedure once for each table name, and capturing the output CREATE VIEW string from the output parameter for each table, in a separate table.
Doesn't that seem like a good use case for output parameters in prepared statements?
Thus, inside the loop, we would have something like this:
SET @proc_call := CONCAT( "CALL mydb.generate_pivot_view_statement('", @curr_table, "', @out_var);" );
START TRANSACTION;
PREPARE stmt FROM @proc_call; EXECUTE stmt ; DEALLOCATE PREPARE stmt;
COMMIT;
INSERT create_view_statements(create_view_stmt) VALUES (@out_var);
Thought I would mention it, in case someone is interested in revisiting the feasibility of output parameters in prepared statements.