Re: Stored Procedures - to use or not to use
> Hm Okay. I see what you're saying, Paul. Is there a preferred way to do > dynamic sql in the database? Or, is the idea that one is better off doing > that which is dynamic in cf? if security/management is a concern, sp are good containers for a lot of stuff. centralized, secure, transparent, etc. these are plenty valid reasons for using sp, especially with sql server 7 & sp_executesql which does buy you some optimization & caching. if speed is important, the trick is to try *not* to use dynamic sql. if you have 3 cases, build 3 sp & have cf decide which one to call (or a container sp which would decide which sp to EXEC). we often go to "ridiculous" lengths to get static sql sp. if we can't we'll build the sql in cf if security/management not an issue. > (Just trying to learn more about enterprise database solutions - not > challenging your viewpoint.) challenge away, i'm by no means an expert. just have some strongly held opinions based on what passes for experience in my neck of the woods. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Stored Procedures - to use or not to use
If you need to build dynamic sql you can use exec(sqlstring) as others have suggested here. There is no reason why this can't be contained within an SP. Just remeber that the sqlstring that you are executing will not be contained within the query plan for the sp. If you need to loop through a recordset and then perform an action based on each record you can do this using cursors although, as someone mentioned, avoid this if at all possible as there is a relatively large overhead on the server. It all depends on how many rows you are selecting into the cursor and how often the sp is called. A far better option is to create temporary tables and perform multiple selects. -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 488 9131 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: Deanna L. Schneider [mailto:[EMAIL PROTECTED]] Sent: 20 February 2001 17:53 To: CF-Talk Subject: Re: Stored Procedures - to use or not to use Hm Okay. I see what you're saying, Paul. Is there a preferred way to do dynamic sql in the database? Or, is the idea that one is better off doing that which is dynamic in cf? (Just trying to learn more about enterprise database solutions - not challenging your viewpoint.) -d Deanna Schneider Interactive Media Developer UWEX Cooperative Extension Electronic Publishing Group 103 Extension Bldg 432 N. Lake Street Madison, WI 53706 (608) 265-7923 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Stored Procedures - to use or not to use
Hm Okay. I see what you're saying, Paul. Is there a preferred way to do dynamic sql in the database? Or, is the idea that one is better off doing that which is dynamic in cf? (Just trying to learn more about enterprise database solutions - not challenging your viewpoint.) -d Deanna Schneider Interactive Media Developer UWEX Cooperative Extension Electronic Publishing Group 103 Extension Bldg 432 N. Lake Street Madison, WI 53706 (608) 265-7923 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
