That is only workaround that I have found also.
----- Original Message -----
From: "Dave Watts" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Friday, February 15, 2002 2:11 PM
Subject: RE: CFPROCPARAM and IN operator
> > I have a stored procedure that is selecting records
> > within a list of values. The stored proc is (abbreviated)
> > CREATE PROCEDURE getSearchResults
> >
> > @time varchar(150 = NULL
> > AS
> >
> > SELECT *
> > FROM table
> > Where time in (@time)
> >
> > GO
> >
> > Here's my coldfusion code:
> > <cfset application.morning = "8:00 AM,9:00 AM,10:00 AM,11:00 AM">
> >
> > <cfstoredproc procedure="GetSearchResults"
> > datasource="#request.mainDSN#">
> > <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR"
> > value="#trim(listqualify(application.morning,"'"))#" null="No">
> > <cfprocresult name="getSearchResults">
> > </CFSTOREDPROC>
> >
> > The problem is that the stored procedure doesn't return results,
> > and I believe its because coldfusion doesn't pass the list of
> > values correctly. I added code in the SP to insert whatever value
> > it was passed into a temporary table, and the values looked good,
> > ie they were 'time1', 'time2', etc. Hard-coding the times works
> > also, of course. But I cannot get any results if I pass the list
> > in.
>
> For most platforms, I don't think this is going to work. There's no list
> data type in SQL, so you can't pass more than one value (a list of values)
> into your stored procedure. The only workaround I know of for this is to
> build a string, and execute that string:
>
> CREATE PROCEDURE getSearchResults
>
> @time VARCHAR(150) NULL
>
> AS
>
> EXECUTE ("SELECT * FROM table WHERE time IN (" + @time + ")")
>
> Note that different platforms have different ways of executing SQL. On SQL
> Server 7/2000, you might use the sp_executesql stored procedure instead.
>
> Dave Watts, CTO, Fig Leaf Software
> http://www.figleaf.com/
> voice: (202) 797-5496
> fax: (202) 797-5444
>
______________________________________________________________________
Why Share?
Dedicated Win 2000 Server � PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
