Get a Return Every Time.

• Cliff Knight

  SSC Rookie

  Points: 38

  More actions

  March 4, 2008 at 10:48 am

  #785077

  rsingh84 (3/4/2008)

  ---

  I am quite surprised that someone thinks querystring based queries are faster than compiled queries.

  Cliff - in your example, you will mostlikely have same performace as a compiled SP since you query is using only a single filter which may be having an index on it. So the performance may be more or less the same.

  Don't be silly, that was an example of using the Replace() function--not a serious example of a dynamic query...

  Using dynamic query is gives rise to SQL Injection - way to hack into your db and even delete your data. So you have to be very carefully and should avoid using it.

  Nonsense...

  A dynamic query inside a stored procedure is no more or less susceptibe to "SQL Injection" than a static query inside a stored procedure.

  As a final comment, I have been writing manage reporting and OLT systems for well over 30 years and have been on the planet for 60 years--if there is any one thing that's made it through my thick head it is that there is NO single best way of doing anything...

  I said in my original post that I have found "dynamic SQL statements to be faster in many situations where the exact nature (and number, when using optional parameters) of passed parameters is unknown at the time a static stored procedure is saved."

  I stand by that statement and suspect those that have not experienced this have not dealt with particularly complex and configurable queries--it's absolutely not true all the time, however it is much more often that one would think.

  This is especially true with contemporary machines having gobs of CPU cycles and memory.