The Proc is looking for a like within the string '[EMAIL PROTECTED]' - you will need to do the following:
SELECT tblprodmodelcodes.prodmodelcode, tblprodmodelcodes.prodmodeltitle,tblprodmodelcodes.prodcategory, tblprodmodelcodes.prodsellprice, (SELECT TOP 1 imagename FROM tblproductimages WHERE tblproductimages.prodmodelcode = tblprodmodelcodes.prodmodelcode ) 'imagename' FROM tblprodmodelcodes WHERE tblprodmodelcodes.prodmodeltitle LIKE '%' + @SEARCH + '%' OR tblprodmodelcodes.prodmodeldescription LIKE '%' + @SEARCH + '%' GROUP BY tblprodmodelcodes.prodmodelcode, tblprodmodelcodes.prodmodeltitle,tblprodmodelcodes.prodcategory, tblprodmodelcodes.prodsellprice -----Original Message----- From: Will Tomlinson [mailto:[EMAIL PROTECTED] Sent: 24 January 2005 02:23 To: CF-Talk Subject: CFQUERY -->-> Stored Procedure I'm converting most of my select queries to stored procedures and hit a snag with one of them. This cfquery works and gives me the desired results for the term "peace": SELECT tblprodmodelcodes.prodmodelcode, tblprodmodelcodes.prodmodeltitle,tblprodmodelcodes.prodcategory, tblprodmodelcodes.prodsellprice, (SELECT TOP 1 imagename FROM tblproductimages WHERE tblproductimages.prodmodelcode = tblprodmodelcodes.prodmodelcode ) 'imagename' FROM tblprodmodelcodes WHERE tblprodmodelcodes.prodmodeltitle LIKE '%peace%' OR tblprodmodelcodes.prodmodeldescription LIKE '%peace%' GROUP BY tblprodmodelcodes.prodmodelcode, tblprodmodelcodes.prodmodeltitle,tblprodmodelcodes.prodcategory, tblprodmodelcodes.prodsellprice I converted it to this stored procedure, and I can't get it to give me ANY result at all. Anyone have an idea as to why? CREATE PROCEDURE sp_getprodsearch @SEARCH varchar(25) AS SELECT tblprodmodelcodes.prodmodelcode, tblprodmodelcodes.prodmodeltitle,tblprodmodelcodes.prodcategory, tblprodmodelcodes.prodsellprice, (SELECT TOP 1 imagename FROM tblproductimages WHERE tblproductimages.prodmodelcode = tblprodmodelcodes.prodmodelcode ) 'imagename' FROM tblprodmodelcodes WHERE tblprodmodelcodes.prodmodeltitle LIKE '[EMAIL PROTECTED]' OR tblprodmodelcodes.prodmodeldescription LIKE '[EMAIL PROTECTED]' GROUP BY tblprodmodelcodes.prodmodelcode, tblprodmodelcodes.prodmodeltitle,tblprodmodelcodes.prodcategory, tblprodmodelcodes.prodsellprice GO My <cfstoredproc> is correct I believe. <CFSTOREDPROC DATASOURCE="#DSN#" PROCEDURE="sp_getprodsearch"> <CFPROCPARAM TYPE="IN" VALUE="#FORM.search#" cfsqltype="cf_sql_varchar" dbvarname="SEARCH"> <cfprocresult name="getprodsearch"> </CFSTOREDPROC> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware: 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:191522 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
