Well, you're not going to be able to use the CF code in the stored procedure. You need to write whatever dynamic aspect using the stored procedure language of your chosen DBMS (Transact-SQL in SQL Server, PL-SQL in Oracle, etc.). More than that, since the decisions will be based on incoming form fields (and the stored procedure won't inherently having access to that), you'll need to pass those form variables into the stored procedure (on the call to the SP). Does that help?
/charlie -----Original Message----- From: Bosky, Dave [mailto:[EMAIL PROTECTED]] Sent: Monday, January 14, 2002 4:58 PM To: SQL Subject: Converting query to stored procedure. I'm trying to convert this cold fusion query to stored procedure but can't get the correct syntax due to the dynamic where clause. Any help would be really cool. <CFQUERY NAME="qResultsOfS" DATASOURCE="HTCYP" BLOCKFACTOR="100"> SELECT CustomerID,Telephone,AreaCode,BusinessName,Address, City,State,ItemCode,Website,Ad FROM CustomerInformation WHERE 0=0 <CFIF IsDefined("FORM.COpt") AND FORM.COpt is not "--"> AND City = '#FORM.COpt#' </CFIF> <CFIF IsDefined("FORM.CTB") AND FORM.CTB is not "--"> AND Classification = '#FORM.CTB#' </CFIF> AND #FORM.SOpt# like '%#FORM.SK#%' ORDER BY Classification, #FORM.SOpt# </CFQUERY> Thanks, Dave ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
