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
