Having just finished a project where this issue cropped up, the solution is
to not use QuotedValueList(Query.Column), but to use
ValueList(Query.Column).
If you use QuotedValueList, you get something like this: '1','2','3'
The IN clause of your sql statement is saying where Integer value a.friendID
is equal to character string '1', '2', or '3'. So, SQL is doing exactly
what it's supposed to do, it's trying to convert the string to an integer
for comparison purposes, but this fails.
If you use ValueList, you get something like ths: 1,2,3
So now you're IN clause is comparing Integer value a.friendID to a list of
integers. And the processing should proceed fine.
Of course, if you are doing Dynamic SQL in your stored procedure, there's a
few other hiccups you have to deal with (such as quotes ending a string and
such).
HTH
Shawn Grover
-----Original Message-----
From: Costas Piliotis [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 30, 2001 4:00 PM
To: CF-Talk
Subject: RE: Stored Procedure
See though, it's not that simple for this problem.
They're trying to parse a comma delimited list...
They should use dynamic sql to do what they're trying to accomplish.
-----Original Message-----
From: Matthew W Jones [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 30, 2001 2:37 PM
To: CF-Talk
Subject: RE: Stored Procedure
friendID is an int, therefore, you must compare it to an int for it to
match. sql is trying to convert it to an int for you, which isn't working.
you might be able to get away with a valuelist, instead of a quoted value
list.
-----Original Message-----
From: Fuon See Tu [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 30, 2001 4:12 PM
To: CF-Talk
Subject: Stored Procedure
hey guys, in the WHERE part of my stored procedure in the SQL 7 database, it
has something like a.friendID IN (@funnyID)
@funnyID is declared as a varchar in the procedure. in coldfusion, the
value is passed as a Quoted value list, like '54','56','53' and so on. the
problem is, i get this SQL error when i run the coldfusion template
Syntax error converting the varchar value '181', '182', '183', '217' to a
column of data type int. why does it need to convert them to int?
i mean if the SQL was done right o the coldfusion template, then that would
be fine. why is it different now that its in the stored procedure?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
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
