Not sure I agree with that. I like to keep my DB stuff in the DBMS, and
out of CF. But that's somewhat of a philosophical argument. (whether
CF is made smart enough to vary the query, or if the DBMS has to do it,
someone still has to expend the effort, so it's 6 of 1, 1/2 dozen of the
other in that respect)
Besides, if you are passing stuff like this:
<cfquery datasource="blah" name="whatever">
select field1, field2 from table where field1='#Form.FirstName#'
</cfquery>
This above query is still dynamic, as far as the DBMS is concerned. It
still has to parse it out and create a plan before executing it (the
expense is still there).
Either way, you definitely have to watch out for SQL injection!!!!!
If your queries are simple enough, you could do it with static query
expressions in a stored proc that are similar, chosen by a big IF
condition within the stored proc. This would be compiled once and
stored within the DBMS for very fast execution later. However, I don't
know of any way to do this in SQL Server when using the IN() operator
passed to a stored proc. That is why I went dynamic.
-----Original Message-----
From: Robertson-Ravo, Neil (RX)
[mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 22, 2005 03:35
To: SQL
Subject: RE: How 2 convert cfquery to TSQL (MS SQL stored proc)
If you are going down the dynamic SQL route you are better off keeping
it in
CF. Dynamic SQL is very inefficient and should be avoided wherever
possible.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2145
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
