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
