> > BUT, be careful with EXEC ...you can incur some pretty
> > significant performance hits ..... it messes up the SP
> > plan caching, i.e. losing one of the major benefits of
> > SP's ...speed.
>
> Use sp_executesql instead of EXEC() to run your dynamic SQL.
> It caches the query plans.
If you're using dynamic SQL, you might not want to cache your query plans.
If your dynamically-generated SQL varies enough (using different tables, for
example), one plan won't be appropriate for another SQL statement. In
general, you want your stored procedures to do simple, atomic operations -
if you need to execute complex conditional logic within your SPs, you can
modularize your SPs so that the conditional logic is in an "outer" SP, which
calls the appropriate "inner" SP - which will have its own individual (and
appropriate) query plan.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
