Menu
I would like to create a stored procedure in MySQL that took a list as argument. For example, say that I would like to be able to set multiple tags for an item in one call, then what I want to do is to define a procedure that takes the ID of the item and a list of tags to set. However, I can't seem to find any way to do this, there is no list datatype, as far as I'm aware, but can it be emulated somehow? Could the list of tags be a comma-separated string, which can somehow be split and looped over?
How do you usually work with lists in MySQL stored procedures?
633
Depending on the programming language and API, you can pass the list in as a table valued parameter (TVP). Other solutions will vary depending on your SQL Server version. See sommarskog.se/arrays-in-sql.html.
Because MySQL doesn't have any type Array or List , we need to find a workaround : a common alternative is to consider the array as a string where each value would be separated by a comma. Then you can concatenate the said string with the rest of your query and use it in a prepared statement.
ALTER VIEW . LOAD DATA and LOAD XML . SQL prepared statements ( PREPARE , EXECUTE , DEALLOCATE PREPARE ) can be used in stored procedures, but not stored functions or triggers. Thus, stored functions and triggers cannot use dynamic SQL (where you construct statements as strings and then execute them).
Stored routines cannot use LOAD DATA INFILE . Statements that return a result set cannot be used within a stored function. This includes SELECT statements that do not use INTO to fetch column values into variables, SHOW statements, and other statements such as EXPLAIN .
This article has some good discussion on the problem of parsing an array to a stored procedure since stored procedures only allow valid table column data-types as parameters.
There are some neat things you can do with the csv table type in mysql - that is if you are loading a flat file into the db.
You could create a temporary table in the stored procedure, iterate over the csv list and insert it to the temp table, then create a cursor which selects the values from that table. This answer in the above mentioned thread shows a way of doing this.
Generally I would split the array before I come to the database and then perform the query individually on each item.
140
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
