Can I pass a query statement to stored procedure in Mysql-CodePudding

I'm new to MySql stored procedure. Supposed I have a table like this

my_table
mz   intensity
217   0.1
218   0.9
219   1
220   0.5

I want to pass these values to a stored procedure and use them in there, for example:

I tried to call stored_procedure(select mz, intensity from my_table) as in parameter but that didn't work.

CodePudding user response：

No, you can't pass a query result or a table to a procedure.

https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html says:

Parameter types and function return types can be declared to use any valid data type.

By data type, they mean one of the scalar data types supported by MySQL. A query doesn't count as one of these data types. Nor does MySQL support any data type for a "collection" like an array or table type.

If you need to process a query result in a procedure, you need to run the query in that procedure.

You can pass a query as a VARCHAR (string), and use dynamic SQL to execute it in the procedure. Read https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html for information and code examples.

Using dynamic SQL in a procedure is risky, if you aren't in control of the exact query string you pass to the procedure. For example, DO NOT take input from an untrusted source and pass it to your procedure to be executed as a query. That's a sure way to get hacked.