In my opinion procedures should not be used to implement game logic.
First off its pretty uncomfortable to write or modify them, you would need to
drop/create or overwrite them all the time while you're developing and testing them.
Also i find sql syntax somewhat odd to write large portions of logic in it.
Performance wise its easily possible to create more load on the database then needed to do the same with php.
I'm assuming you'll have almost everything needed already queried in php for visualization and if its only to display the stats of character, weapon, monsters etc. so one attack would be just one update query in your database even less when you're using session storage or some other kind of temporary caching.
Dont get me wrong procedures have their use, but i think/assume when writing a game you'll need almost any affected data in application code too so having procedures change your datasets so you have to requery them isnt the best idea if your game is not beeing played on a sql client terminal session.
I just want to really quickly say this in response... as long as you are not using loops, and can do enough logic inside of the stored procedure, it will be very beneficial if you are running a database based game... and unless you write incredibly horrible code that SQL cannot optimize over time it will not overtax the database if it wouldn't overtax the php server... assuming that you avoid loops... and your table doesn't incur a few million entries each day (historic data... takes a very long time to query when in the right company, and while it doesn't freeze the database if well written there is just so much of it).
That said, based off of a few conferences that I have went to, I have had much more experience with the pros and cons of stored procedures than all of the other php developers that were there (which made me really sad), and I can understand that it can indeed be very difficult to figure out how to properly make them. The if statements can be a bit much, and not sure how they tax mysql versus mssql...
What was said is correct, you could do it all in an update. However, for security purposes if nothing else I would put it into a stored procedure that performs the update.
Also wanted to note a few procedure practices that are a good standard for development and deployment.
Save each procedure to its own file (I use MySQL work bench with mysql, which makes it really easy to do such things, then can write a batch if needed to put out to a new server, or simply update). This way you have quick access, and can easily apply version control.
Use the DROP IF EXISTS procedure_name; statement at the beginning of your procedures, as it will make sure that you don't
Of course, every time you have something you think works you should put the new version into your version control.
If you follow those steps, procedure development/deployment is fairly simple...
However, i do understand what you are saying. I guess optimal deployment would depend upon how many times you may have to loop over data/how complicated changes you are making are. Also I had in my head a kind of form based game when I replied... but depending upon security requirements it may be best to use Ajax continuously and place extra security checks into the PHP, then not worry about those same checks in the database and only update the database at the end.
Correct me if I am wrong though, but if you wanted to make the game able to have user interactivity, wouldn't it need to keep the database updated constantly anyway for a database driven game? In my head I have a few [older] mmo games running in my head and can see the traffic flowing with small tables per section to quickly give the current data out. Even if you have a reasonably horrible database server, you should easily be able to have a table with 1000 rows of data or less going nearly instantaneously for database transport.... and I haven't done a lot of Ajax, so I may be wrong but it feels like it would be possible to keep the connection open and just have it continuously echo out an xml stream to keep up to date, while checking the database...
Hmmm....guess I have a new thing to try in my free time.
I just said 1000 rows above because it seems that initially the project may start with such a (relatively) small amount of data.
For me it just seems to make sense that if it isn't client side, then why shouldn't it be updated in a place where everyone in the game can access it as soon as possible. However, if it would be faster to do the PHP then an update statement then that would be the way to go. (Though update procedure for security still)
Just want to end my rambling with this: I feel that logic in sql is something that every programmer should have to learn, as it teaches you to think much differently and forces you to come up with algorithms that will perform without looping if you want your sql database to work well. There are some pretty crazy requirements that can occur, and creating a query that can complete them quickly and properly is something can force you to think outside the box, and improve your O time in the long run.