I know how to do CTEs, sorting, paging, all of that stuff. That is not the issue. The issue is there is no benefit to having this all in stored procs other than the fact that you get to lock down by execute permission only. IF that is your goal in life, then fine, do it. But there is a lot of benefit to be had by using dynamic SQL generation, so long as you are protecting against injection.  There is no need to throw everything in a proc, especially read only queries using strongly typed parameterized statements.  I generally do all update or insert code in procs, and I will do complicated queries I construct with Lambdas using LINQ. This is so much faster to develop, doesn't litter my code with SQL, and look, it's just about as secure as your way.  I don't understand what the huge deal is with everyone saying 'must stored procedure!'  I have been doing this for 15 years, I'm not an idiot, I have seen the shift in opinion from one way of doing this to the other more than once.  It's just stupid to say that you have to always do stored procs. My complexity examples were just that, examples. Maybe not the greatest examples, but I was trying to illustrate that if you have to change just one little thing, like maybe I use conditions 1 and 12, and next time I use 25,26, and 27, it's ridiculous to put that all in a proc, when i'm just doing some filtering. The reason I throw up the idea of the server side paging is because the typical solution is to draw it all back and filter client side, which i hate, but throwing that on an already complicated multiple parameter proc just makes it that much moreso.  i wasn't implying that I, you, or anyone, doesn't know HOW, I'm saying that the idea that you MUST do it in a proc is ridiculous.

What are you talking about? Tell me how you would be so flexible as to encapsulate 100 optional ways of filtering your rows, with a user driven query interface to report on your data, optionally sorting on columns in both directions, while paging it server side without writing a combinatorial number of stored procs or one with 1000 parameters.  Huh? I'm waiting.   Stored procs have a place, they're fine, and I'm not opposed to them. But they are not a religious relic.  They are just what they are, an option.  If someone can compromise your app and get at your db info, and you take refuge in the fact that at least your stored procs can only be run (which are the way your database is modified to begin with from your app,) then you have spent too much time worrying about that and too little worrying about securing your web app.  Security chaining can be considered a HOLE in security because it only checks the right to run the proc, which may have side effects galore.  What happens if you can run your proc and change yourself to be an administrator? where is your precious security chaining there?  Get over it.  Say I have a report, where I want to maybe sometimes filter based on the date, and then sort based on the last name, and then i want to limit them based on some other thing. But the next time I run it, I just want to sort descending on the date with a larger page size.  Do I write a proc with 15 parameters?  And in that proc do I do a bunch of if blocks? God help the guy who maintains your apps.

I mean 'order by clauses' not where clauses.

yes, technically you can do that, and I have done that before, but depending on the kind of variation you want, it's more difficult.  Also some dbms will let you parameterize where clauses, and some will let you do a limited 'case' statement on them, and still others won't at all.

  In a query builder pattern you can append nodes (which can be strongly typed to check that it's not injecting things) to be converted to query strings after you're done, but in a stored proc its a load of IF blocks or case statements that are tough to maintain and the query will have a tendency to be non-deterministic anyway.  Stored procs as security construct are ok for CRUD, but they're not actually safer than doing it the way I suggest. It's just a different, and in my opinion, more difficult to implement and maintain, way.

Stored procedures are not the cure-all for everything. They are good if you have only a few ways of doing things, but it's ridiculous to write a different stored proc for every single column that you want to sort by. Its stupid to write a new stored proc for every possible way of varying the query. Yes they do guaruntee some kind of type checking and parsing compliance, but you can do that with a prepared statement as well. Dynamic SQL is a lot more flexible, especially when the number of stored procs would be combinatorial in number. You just have to be smart, and know what to do. Try converting your values to the types you want. Make your own parser if there is no other way, but for example, in the .NET world you can use ADO.NET with the typed parameters on text queries and it's every bit as safe and efficient as a stored proc. I'm not sure how well or not this translates to PHP and MySQL but I think the db module has most of the same stuff, if I recall correctly.