Hi Ed, Thanks for the feedback. I can do this easily in Cold Fusion, what I'm trying to do is offload the work to MS SQL 2000 via TSQL in a stored procedure. The search form has a lot of entry fields, we're doing a zipcode radius search too, so it just makes more sense to have the DB do the work rather than CF for speed.
So the goal here is to have MS SQL 2000 do the work instead of CF. And my problem... ahem... challenge... is converting this logic into TSQL :( Thanks for your response though:) ----- Original Message ----- From: "Eric Creese" <[EMAIL PROTECTED]> To: "CF-Talk" <[email protected]> Sent: Monday, February 21, 2005 12:44 PM Subject: RE: How u convert this cfquery to TSQL for MS SQL stored procedure > may not have to do that if you try the following > > <cfquery name="test" datasource="abcinc"> > select * > from profiles > where 0=0 > <cfif isdefined('form.firstname')> > <cfif form.firstname neq ''> > and firstname='#form.firstname#' > </cfif> > </cfif> > <cfif isdefined('zipsreturned') > <cfif zipsreturned neq ''> > and zipcode in (<cfqueryparam cfsqltype="cf_sql_varchar" value="#zipsreturned#" list="yes">) > </cfif> > </cfif> > </cfquery> > > -----Original Message----- > From: Pardeep Singh [mailto:[EMAIL PROTECTED] > Sent: Monday, February 21, 2005 1:32 PM > To: CF-Talk > Subject: How u convert this cfquery to TSQL for MS SQL stored procedure > > > Does anyone know how to convert this cfquery to TSQL so it can be executed through a stored procedure instead in MS SQL 2000? The first condition just sees if a var exists and makes sure its not null, the second one does the same thing but feeds the query a list: > > <cfquery name="test" datasource="abcinc"> > select * > from profiles > where 0=0 > <cfif isdefined('form.firstname') and form.firstname neq ''>and firstname='#form.firstname#'</cfif> > <cfif isdefined('zipsreturned') and zipsreturned neq ""> > and zipcode in (<cfqueryparam cfsqltype="cf_sql_varchar" value="#zipsreturned#" list="yes">)</cfif> > </cfquery> > > I'm having a tuff time creating the condition in the WHERE part to check if the variable form.firstname exists first, and then to make sure its not null. > > I'm trying to build a search form and then have the search criteria queried in a MS SQL Server stored procedure directly for the sake of speed. > > I appreciate any help. Thanks. > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:195805 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
