I'm working on my first Stored Procedures in SQL Server and using CF 5 to interact with them.
I'm getting an error in CF when including a <cfprocparam>. "ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to int." The <cfprocparam> code is: <cfprocparam type="In" dbvarname="product_id" value="#attributes.product_id#" cfsqltype="CF_SQL_INTEGER"> Now, I also have the following <cfprocparam>: <cfprocparam type="In" dbvarname="category_id" value="#attributes.category_id#" cfsqltype="CF_SQL_INTEGER"> If I take out the product_id parameter, it runs fine [I have a default value in the SP] (meaning there's no problem with the category_id parameter). When I output the values of the 2 attributes, both of them are 0 (as they should be). But it seems to choke on the product_id parameter. Here's part of the SP code: CREATE PROCEDURE qryProductsProc @category_id int = 0, @product_active int = 1, @product_id int = 0 AS SELECT p.product_id FROM productTbl p, product_typeTbl pt, product_categoryTbl pc, product_priceTbl pp, categoryTbl c WHERE p.product_type_id = pt.product_type_id AND p.product_id = pp.product_id AND pp.active_code = 1 AND pc.category_id = c.category_id AND p.product_id = pc.product_id AND p.active_code = @product_active AND (@category_id = 0 OR pc.category_id = @category_id) AND (@product_id = 0 OR p.product_id = @product_id) ORDER BY p.product, p.modified_date GO Any ideas on why CF (or SQL Server) thinks the product_id is a string? Even putting in VAL(attributes.product_id) results in that error. Thanks! Scott ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists