candidate for stored procedure?

Question

I have the sql query below. I want to take the results and insert it into another table using the insert below. Is this a good choice for a stored procedure.

It doesn't need to run very often, so no need to attach it to a trigger... I'll probably just write a php script to trigger it via an hourly cron job. (or maybe I can trigger it via command line)

So, am I barking up the wrong tree on this one? Thanks Oh, I'll accept any helpful answer.

 SELECT
  COUNT(*) AS thecount,
  MAX(datetime_acc) AS DATE,
  u.created_usr,
  @payout:=IF(u.created_usr < '2011-01-24',20,10) AS payout,
  level_usr,
  @uid_usr:=p.uid_usr,
  @affiliate:=u.affiliate_aff,
  created_usr,
  firstname_usr, 
  lastname_usr,
  contact_aff,
  c.id_com

FROM payment_acc p
 LEFT JOIN users_usr u ON p.uid_usr = u.id_usr 
 LEFT JOIN commissions_com c ON c.uid_usr = u.id_usr
 LEFT JOIN affiliate_aff a ON a.code_aff = u.affiliate_aff




WHERE p.type_acc = 'monthly payment'
AND affiliate_aff IS NOT NULL
GROUP BY p.uid_usr
HAVING thecount > 1

ORDER BY affiliate_aff

The INSERT:

Insert into commissions_com 
    date_generated_com,
    amount_com,
    uid_usr, 
    code_aff, 
    status_com
values
   (NOW(),
    @payout,
    @uid_usr,
    @code_aff,
    'new')

Answer 1

Using stored procedures is a strategic decision; it should not be taken lightly.

MySQL stored procedures are very limited and have a lot of drawbacks:

• The language is very weak, particularly before 5.5 where there is no clean way of raising an error
• Some things cannot be done without using SQL prepared inside the stored procedure, which is an incredibly nasty pattern and very error-prone.
• There are absolutely, categorically, no debugging facilities
• They have only a fixed number of mandatory positional parameters. Changing the interface to a procedure in a backwards-compatible way is not generally possible. So you end up with a registerAccount procedure, and a registerAccount2, and registerAccount3 etc, because you needed to add more parameters later, but couldn't instantly change all code calling the procedures (as of course, it's spread out on different machines throughout your infrastructure and you need to carry out a rolling upgrade).
• Code management / change control - having stored procedures adds an extra piece of code to try to control (in development, test environments, production, etc)

For me, these are all good reasons not to use stored procedures. They're difficult to write (the language is weak), you have to write code that uses "bad" patterns, and they're difficult to write correctly (hard to debug, it's difficult to know what's happening inside).

Also many people cite some benefits

• Security - only a benefit if you get your stored procedures right, and have a well thought-out- security model in your entire application
• Performance - a strategic decision based on performance is a VERY premature optimisation. In most cases ad-hoc SQL performs as well as the same in stored procedures. Multiple-statement latency can be reduced by using a client which supports CLIENT_MULTI_STATEMENTS and combining several queries into a single request message.
• Abstraction / reuse. Unfortunately the extreme difficulty of getting structured data into / out of procedures makes this hard too. It might be useful if you have a really well designed system. It is often easier to write subroutines in your own front-end language and use those instead. Or use an application server which allows a richer interface.

Answer 2

What "value" are you looking for?

Jeff Atwood has for a long time questioned the use for stored procuedres.

From the mysql documentation:

MySQL 5.0 introduced Stored Procedures which allow us to automate or program our way out of many tasks on directly on the server rather than having to write external scripts to do complex manipulation of data.

As you get used to writing stored Procedures in MySQL 5.0, you will, as with any other programming language, want to generalize your stored procedures as much as possible. The more flexible your stored procedure is, the more tasks it can be used for -- and the less places you have to go searching for that elusive bug that just keeps giving you the wrong result. The day you end up making a copy of a stored procedure just to change a name or two is the day you need to think how tweaking the original procedure can accomplish what you want without breaking old functionality.

From what you have described, your script doesn't sound general or to be used for several tasks.

For the time being then, I would avoid a stored procedure.