MySQL stored procedures: #1172 – Result consisted of more than one row

I’ve been playing with MySQL’s stored procedures for a few months now. I like how they let me reduce the traffic between the web server and the database server, and how they help make the PHP code more readable. I’m not a big fan of OOP (I do use it when it’s really helpful, though, but I try to limit it) so it’s always good to have other neat ways to clean the code. However, stored procedures still have a few shortcomings. Notably the fact that unlike some urban myth says, it seems that MySQL doesn’t precompile its procedures (I don’t remember where I read that, sorry), for the moment at least (I guess it’s somewhere in the to-do list for far-far-away future development). But also the fact that you can’t replace everything with them.

For instance, say you want to use a query like SELECT * FROM some_table WHERE some_filed IN(“a value”,”another value”,…,”and another value”). Well, unless you know from the beginning how many values you’ll have in the IN(…), and create one input variable for each of them (dirty, dirty!), you can’t feed them to your procedure: it doesn’t take arrays. I forgot all the things I found, as what I retained for my use is that I’ll keep building the queries in PHP when such cases arise, but for the bravest here are some dirty hacks you can do to bypass this limitation.

Another problem is that it can’t deal with queries returning more than one row (except for the case when that query is actually the end of the procedure). So if you want to do something like this:
SELECT id_thread INTO tmpDiscID FROM pm_threads WHERE id_member_started=id_auteurIn AND id_thread>minimalDiscID AND date_heure_creation=date_heureIn AND titre=titreIn AND member_count=member_countIN;, you’d better be damn sure it will return only one row, or add LIMIT 1 (but the latest only works if you know you want the first resulting row). About this, you can check out Stackoverflow – Sending a list of values of the same field to MySQL stored procedure, Stackoverflow – How to retreive multiple rows from stored procedure in mysql? and MySQL forums – Re: New to sp, ERROR 1172 (42000): Result consisted of more than one row.

Well, that’s it for this post. Sorry only issues no real solution, apart from: just don’t make it a point to do everything in stored procedures. Some things just can’t be done this way, or at least not in an efficient (so, not in a worthy) way.