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:191503
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
