> I'm not sure if you're saying I should avoid the use of
> EXECUTE STATEMENT or not.

I am not really advicing on the actual choice. But my opinion is that it is best to not use the EXECUTE STATEMENT. Neither is it good to use straight SQL. (The are essentially equal on all accounts). If they are reasonably avoidable.

The god thing with the above is that they are pretty straightforward and will work quite easily albeit not so fast. But the main drawback of both of them is that they lack any kind of typechecking wich makes them prone to errors and you don't get the advantage of seeing dependencies etc.

So, the good approach would be to make a lot of parameters to a SP and use them wisley in there probably with a lot of coalesce and conditions (depending of course on what you are doing).

This is almost always a possible approach but it might end you up with a large, unmaintainable SP that you can hardly understand yourself. Since we lack true case logic it might also end up having duplicated code wich is no fun in terms of maintainability.

So essentially it comes down to how uggly will the SP be in the end? Most often maintainability should be put first but the first two options are less safe in terms of introducing errors in the SQL code. Neither option is the silver bullet.

>For the time being I am still
> trying to do various speed benchmarking against the other
> two approaches (straight SQL queries and layered IF NULL .. SPs).

I don't ofcourse know what you are doiong but might you be focusing too much on speed? Twice as fast often mean 2ms instead of 1ms.
Atleast weight in maintainabillity, safety as well as speed when you choose.

I don't know anything about you last problem but now I am leaving for weekend so good luck trying.

Tim