Is this another typo? What "ADD" statements? /charlie
-----Original Message----- From: Bosky, Dave [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 4:30 PM To: SQL Subject: RE: Converting query to stored procedure. Sorry that was a typo on my part that I corrected. It should read -> AND @SOpt like '%@SK%' It seems to bomb around the ADD statements that are in the IF statements. Thanks, Dave -----Original Message----- From: charlie arehart [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 4:22 PM To: SQL Subject: RE: Converting query to stored procedure. Well there you go: the last line of the WHERE clause says: AND @SOpt like '%#FORM.SK#%' You can't reference CF variables in the SP. But you are passing in the value as @SK (it would seem). Why not use that? I'm guessing one of us is going to say "Doh!"--and it could be me if I'm just missing something completely. :-) /charlie -----Original Message----- From: Bosky, Dave [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 8:13 AM To: SQL Subject: RE: Converting query to stored procedure. I should have mentioned I was trying to convert this query into a Transact-SQL stored procedure for SQL Server 7. This is the code I have so far that's not functioning. ----- CREATE PROCEDURE spSearchResults --optional : search by city @COpt [varchar] (50) = NULL, --optional : search by classification @CTB [varchar] (80) = NULL, --required : search option(BusinessName, Address, Telephone) @SOpt [varchar] (50), --required : search keyword @SK [varchar] (80) AS SELECT Telephone,AreaCode,BusinessName,Address,City,State,ItemCode, Classification,Website,Ad FROM CustomerInformation WHERE 0=0 IF (@COpt IS NOT NULL) OR (@COpt IS NOT '--') BEGIN AND City = @COpt END IF (@CTB IS NOT NULL) OR (@CTB IS NOT '--') BEGIN AND Classification = @CTB END AND @SOpt like '%#FORM.SK#%' ORDER BY Classification,@SOpt ------ Thanks, Dave -----Original Message----- From: charlie arehart [mailto:[EMAIL PROTECTED]] Sent: Monday, January 14, 2002 6:38 PM To: SQL Subject: RE: Converting query to stored procedure. 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 ______________________________________________________________________ Get the mailserver that powers this list at http://www.coolfusion.com Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
