you pass the list in? Make sure the list delimiter is a comma.
Try this...
<CFPROCPARAM DBVARNAME="@FOLDERLIST"
VALUE="#PreserveSingleQuotes(attributes.delete_list)#"
TYPE="IN"
CFSQLTYPE="CF_SQL_VARCHAR">
HTH,
Mike
-----Original Message-----
From: John mccosker [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 27, 2004 6:56 AM
To: CF-Talk
Subject: Passing List into Stored Proc
Hi,
I have wrote a stored proc that will delete items in a table by do
a subquery on a list passed into it.
If I test it in Query Analyzer it runs effectively,
DELETE
FROM DBO.a
WHERE DBO.a.FOLDERNAME IN ('jim','ben','tom')
AND DBO.a.CUSTOMERID = 1
but when I call through cf passing in the list it does not,
cf
----
<CFSTOREDPROC PROCEDURE="p_test"
DATASOURCE="#request.d#"
USERNAME="#request.u#"
PASSWORD="#request.p#">
<CFPROCPARAM DBVARNAME="@CUSTOMERID"
VALUE="#ListGetAt(cookie.c_userSession,1,';')#"
TYPE="IN"
CFSQLTYPE="CF_SQL_INTEGER">
<CFPROCPARAM DBVARNAME="@FOLDERLIST"
VALUE="#attributes.delete_list#"
TYPE="IN"
CFSQLTYPE="CF_SQL_VARCHAR">
</CFSTOREDPROC>
proc code
---------
CREATE PROCEDURE p_test
(@CUSTOMERID INT,@FOLDERLIST VARCHAR(2000))
AS
DELETE
FROM DBO.a
WHERE DBO.a.FOLDERNAME IN (@FOLDERLIST)
AND DBO.a.CUSTOMERID = @CUSTOMERID
running ms sql 7.0 and MX 6.1
Thanks for any help.
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
