Yup I agree Kevan.....as far as I know you can't dynamically name any
objects directly in your sql statement (tables, fileds, databases, etc...).
The way I get around it is to do as you mentioned - build a string which you
execute using Exec(string). That way the variables get evaluated before the
sql string is executed.
------------------------------------------------------------------
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: Windle, Kevan [mailto:[EMAIL PROTECTED]]
Sent: 28 February 2001 15:08
To: CF-Talk
Subject: RE: Stored Procedure Question (Sybase)
I'm not sure about Sybase, I suppose it's the same principle as in SQL
Server. You have to either generate a dynamic query, by building a string
and running it via the execute function, or have a conditional statement to
select one query or another depending on the sort variable passed. The first
option is more flexible the second more efficent.
-----Original Message-----
From: Sandi Benditt [mailto:[EMAIL PROTECTED]]
Sent: 27 February 2001 19:28
To: CF-Talk
Subject: Stored Procedure Question (Sybase)
We are in the process of moving all our queries to stored procedures, but
I've run into a problem that maybe someone can help me with.
Take the following query. If I move it to a stored procedure and try to
pass the sort variable to it I get an error along the lines of you can't
use a variable in an order by (I forget the exact wording).
<CFQUERY NAME="detail" DATASOURCE="GVDB">
SELECT
vaba, np_code, partnum, qty, fe_usl, asl
FROM
gvware..mytd_b3f021_parts
WHERE
asl="#url.asl#" and
trans_month="#trans_month#"
ORDER BY
<CFIF IsDefined("SORT")>
#sort#
<CFELSE>
vaba,asl,fe_usl
</CFIF>
</CFQUERY>
Can anyone help?
--
Sandi Benditt
Sr. Programmer Analyst - Advanced Technologies
DecisionOne
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
