I have a stored procedure which is retrieving data using 20 tables. Sample of the procedure:
Please forgive me, if my question is NOT clear or not a proper question.
Thanks for your valuable time & help.
asked Nov 16, 2011 at 10:26 AM in Default
In general, instead of building a string and executing it, which can be subject to SQL injection attacks, I'd suggest building a string with parameters and using sp_executesql to execute it, providing the parameters. It's more work, but it's more secure, AND, it's more likely to see improved plan reuse.
No, the query is the long part of the execution. Building a few strings is pretty pain free.
In terms of summary columns, no, that's an extra pass. Instead I'd perform a join against a sub-select (usually, sometimes breaking it down into steps can work better).
Nope. See above. sp_executesql is better.
Yeah, plan it out as a single select statement. The temp table is just holding for the aggregation which can be done as a derived table.
Optimize? Tougher question. Just seeing what you have, the CONVERT statement on e.EnquiryDate is going to cause a scan, no matter what. If that's a datetime column, you need to compare that to a datetime value, no conversions. Beyond that, I couldn't say without seeing the whole queries, execution plans, data, structure, all that.
In general, these catch all type queries are extremely problematic. Instead of doing this, identify the common patterns that will inevitably exist, these three columns always come in, this one only comes in when another one comes in, etc., and then build three or four different procs that take take into account these patterns and use this as a wrapper proc to determine which of those procs it should go to. It'll be work to set up, but no more than this, and it will perform better and will be easier to maintain.
answered Nov 16, 2011 at 11:36 AM
Grant Fritchey ♦♦