I'm going to skip past your questions and try to offer broader guidelines/advice instead.
The definitive/canonical guide to dynamic SQL, the situations where it is applicable and where it can be avoided, is Erland Sommarskog's Dynamic Search Conditions in T-SQL. Read it, re-read, run through Erland's examples, make sure you understand the reasoning behind the recommendations.
You're dealing with a fairly common scenario and the approach you've taken is not unusual. A couple of points worth highlighting:
- Using temporary tables is probably unnecessary. Is there a reason they were introduced?
- You have probably over-indexed the table. Read Kimberly Tripp's "just because you can, doesn't mean you should" article on the topic.
- Because you've over-indexed on individual columns, you're probably lacking good covering indexes. With so many aggregations and such a wide range of search conditions, these will be a challenge to get right.
Now the most important part of getting these kinds of searches right... apply the 80/20 rule.
The majority of calls to your procedure are likely to comprise a relatively small number of the possible variations of parameters. You cannot create optimal indexes for all combinations of 15 parameters, so identify the most common patterns, create static stored procedures for these and index for them appropriately. Deal with the remaining combinations with dynamic SQL, following Erland's best practices.
In these scenarios, you will often find the usage patterns closer to 95/5 than 80/20 so the additional work of creating static procedures is not as labour intensive as it seems at first glance.
You can create stored procedures that reference objects that don't exist yet (e.g. tables and functions). You cannot create stored procedures that reference columns that don't exist yet in objects that do already exist. This is the double-edged sword of deferred name resolution - SQL Server gives you the benefit of the doubt in some cases, but not all. See Erland's ideas for SET STRICT_CHECKS ON; to get some ideas of the places this works and the places it breaks:
http://www.sommarskog.se/strict_checks.html
(And how he'd like the polar opposite of what you're after - you want to allow anything to compile regardless of existence, and he wants every single column or table to be checked.)
There is no setting like SET DEFERRED_NAME_RESOLUTION OFF; though it has been asked for:
http://connect.microsoft.com/sql/127152
And there is no setting like IGNORE ALL_RESOLUTION;.
You could get around this in a few ways, including:
(a) use dynamic SQL in the affected stored procedure(s).
(b) build a stub for CREATE PROCEDURE with nothing in it, then run the rest of your script, then run an ALTER PROCEDURE which has the real body (in essence, deploy the procedure in two phases).
(c) make your deployment tool smarter about the order of operations. If table changes require the presence of a function, script those changes last. Schema comparison tools like RedGate's SQL Compare are pretty good about generating scripts for you in the proper dependency order. You don't mention what tool you're using, but if it's not doing this...
(d) Martin Smith has an interesting workaround here, but I haven't played with it.
Best Answer
Just hearing the question makes me think of two aspects:
ASPECT #1 : Functions are supposed to be DETERMINISTIC
If this so, this implies that a function should present the same return data consistently for a given set of parameters, NO MATTER WHEN YOU CALL THE FUNCTION.
Now, imagine a function that produces a different answer because of gathering data at different times of the day based on static SQL in the function. In a sense, that can still be considered DETERMINISTIC if you query the same set of tables and columns every time, given the same set of parameters.
What if you could change the underlying tables of a function via Dynamic SQL? You be violating the definition of a DETERMINISTIC function.
Notice that MySQL added this option in /etc/my.cnf
Although this may be an oversimplification to say, this allows functions to be allowed to write data into binary logs without strictly enforcing the DETERMINISTIC property.
ASPECT #2 : Triggers should be able to be rolled back
You would essentially have data that grows quadratically (even exponentially) just in MVCC alone. The process of managing the rollback of SQL with triggers that can be non-DETERMINISTIC would be ungodly complex, to say the least.
In light of these two aspects, I'm sure MySQL Developers thought of these things and quickly dismissed them by imposing restrictions.
So, why lift the restriction for Procedures? Simply put, there is no concern over DETERMINISTIC properties or Rollback.