Here is my problem.
I have a query which I pass a comma-delimited list and
use the IN predicate to return only the rows I want.
Now I want to convert it to a stored procedure.
How do I send that comma-delimited list and then
process it as type int in the stored procedure?? What
I currently have appears to see the first facility
but not the rest.
BOL says that the datatypes much match. What if I put
the list in an array? Can I pass that to the stored
procedure? What do I do with it there?
Code follows
=============
Where Auth_Facility is "1,2,3,4,5" which matchs the
FacilityIDs I want to return
Current query:
<CFQUERY NAME="GetAnnouncements" MAXROWS="5"
DATASOURCE="#Application.DataSource_#">
SELECT * FROM Announcements
WHERE ((Announcements.StartDate <=
#CreateODBCDate(Now())#))
AND
((Announcements.EndDate >= #CreateODBCDate(Now())#))
AND
FacilityID IN (#Auth_Facility#)
ORDER BY facilityid, StartDate DESC
==
NEW CF Code:
<cfstoredproc procedure="spShowAnnouncements"
datasource="#Application.DataSource_#"
returncode="Yes">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR"
dbvarname="@EntityList" value="#Auth_Facility#"
maxlength="30" null="No">
<cfprocresult name="GetAnnouncements">
</cfstoredproc>
==
Stored Procedure:
CREATE PROCEDURE spShowAnnouncements
@EntityList varchar
AS
SELECT * FROM Announcements
WHERE (Announcements.StartDate <= getDate())
AND (Announcements.EndDate >= getDate())
AND FacilityID in (@EntityList)
ORDER BY FacilityID, StartDate DESC
==
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
