다음을 통해 공유

Optional Parameters: Adam Hopes I'm Joking, But The Joke's On Me

  • 아티클

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.. :)

     -wp

Comments

  • Anonymous
    July 13, 2006
    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!
  • Anonymous
    July 14, 2006
    The comment has been removed
  • Anonymous
    July 14, 2006
    The comment has been removed
  • Anonymous
    July 14, 2006
    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..
  • Anonymous
    July 19, 2006
    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>)