This is absolutely my last gasp on optional parameters.
Adam Machanic left some syntax in a comment here which shows just how far off base my "last gasp" in this post was.
My syntax produced 594 logical reads to resolve a query while the equivalent dynamic SQL handled the same issue in six logical reads.
I hereby promise not to write performance analyses in public when I'm packing for vacation.
Repeat after me (although I'm apparently the only one here who needs this reinforcement): dynamic SQL is ALWAYS the one true path in an optional parameter scenario.
Thanks, Adam, for setting me straight.
So much for salvaging my dignity.. :)
Have a great vacation, Ward!! And don't worry, we've all put our feet in our mouths once or twice (or about 5000 times, in my case. Sometimes, I just get lucky :))
Looking forward to more of your content upon your return!
What about security? By using dynamic SQL, you break chain of ownership and have to give permissions to the underlying objects. Sure, if you are using SQL 2005 you can use the WITH EXECUTE AS to overcome this, but in 2000, you have to open up yor security...
Building on the execute-permissions concern above, aren't we re-introducing SQL injection as a risk?
I think the injection threat is moot because, in this scenario, there's no user-supplied input going into the dynamic SQL (rememeber Pond's Law #2 -- All user input is inherently evil and never to be trusted). The dynamic SQL is built based on the NULLity status of a parameter to the stored procedure.
SELECT permission to the underlying objects would be required under SQL Server 2K. If this is objectionable, special pupose views could be built and permissions granted to those.
Or, you could use my "recommended" syntax and live with 100x worse performance..
I find the question of security to be somewhat meaningless in most dynamic SQL cases. The VAST majority of systems I've worked with have required little or no security above and beyond basic login permissions on 99% of the columns in the DB. This is probably because I haven't done extensive work in banking/finance, but in the kinds of apps I've worked on, security was never an issue when it came to the data that people needed to dynamically slice and dice using optional parameters and the like...
But of course, SS2005 solves that problem anyway. And since all of my clients are already upgraded I am enjoying forgetting about the SS2000 problems for at least the moment (until my next upgrade project <g>)