> 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
______________________________________________________________________
Dedicated Windows 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=coldfusiona
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
