Sounds like a silly point but try taking the space out between the values in
the list.
i.e. <cfset CustList = "AFAF,AGAX"> then try it in the SQL
------------------------------------------------------------------
Andrew Ewings
Project Manager
Thoughtbubble Ltd
http://www.thoughtbubble.net
------------------------------------------------------------------
United Kingdom
http://www.thoughtbubble.co.uk/
Tel: +44 (0) 20 7387 8890
------------------------------------------------------------------
New Zealand
http://www.thoughtbubble.co.nz/
Tel: +64 (0) 9 488 9131
------------------------------------------------------------------
The information in this email and in any attachments is confidential and
intended solely for the attention and use of the named addressee(s). Any
views or opinions presented are solely those of the author and do not
necessarily represent those of Thoughtbubble. This information may be
subject to legal, professional or other privilege and further distribution
of it is strictly prohibited without our authority. If you are not the
intended recipient, you are not authorised to disclose, copy, distribute, or
retain this message. Please notify us on +44 (0)207 387 8890.
-----Original Message-----
From: Tim Painter [mailto:[EMAIL PROTECTED]]
Sent: 29 March 2001 12:31
To: CF-Talk
Subject: Re: T-SQL and single quote lists
Jeff,
I was just involved in this same discussion on the allaire, er I mean
Macromedia Forums.
http://forums.allaire.com/devconf/Index.cfm?Message_ID=684281
Basically this is supposed to work:
<cfset l="'1','2','3','7330'">
<cfstoredproc datasource="earthquakes" procedure="junk">
<cfprocparam cfsqltype="CF_SQL_VARCHAR" dbvarname="@list" value="#l#"
type="In">
<cfprocresult name="x" resultset="1">
</cfstoredproc>
CREATE PROCEDURE junk @list varchar(100) AS
declare @code nvarchar(1000)
SET @code = 'SELECT * FROM earthquakes WHERE quakeID IN ('+@list+ ')'
exec sp_executeSQL @code
Regards,
Tim P.
----- Original Message -----
From: "Jeff Gombala" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, March 28, 2001 7:14 PM
Subject: Re: T-SQL and single quote lists
> My mistake in copying the code; CustList and request.CustList are the
same,
> and SQL is actually passing 'AFAF', 'AGAX'. I really think the problem is
> that the @CustList in T-SQL is not being evaluated in the "IN (@CustList)"
> statement.
>
> Have you had any success in passing a list into a stored procedure...
>
> Thanks
> Jeff
>
>
> ----- Original Message -----
> From: "Hayes, David" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Wednesday, March 28, 2001 7:04 PM
> Subject: RE: T-SQL and single quote lists
>
>
> > OK, so there's some difference between your hardcoded list and what
> > #PreserveSingleQuotes(request.CustList)# evaluates to. Did you output
it
> to
> > the screen?
> >
> > I notice your code includes
> >
> > <cfset CustList = "'AFAF', 'AGAX'">
> >
> > but in the <cfprocparam> tag you specifically reference
request.CustList;
> > are they the same?
> >
> >
> > Have you turned on debugging to see what SQL is being sent to your db?
> >
> > -----Original Message-----
> > From: Jeff Gombala [mailto:[EMAIL PROTECTED]]
> > Sent: Wednesday, March 28, 2001 5:56 PM
> > To: CF-Talk
> > Subject: Re: T-SQL and single quote lists
> >
> >
> > If I hard code the list into the "IN" statement I get the results I am
> > looking for it I use the parameter in the "IN" statement as such "IN
> > (@CustList)" the query returns nothing not even any errors. It is
almost
> > like @CustList is not being evaluated inside the ().
> >
> >
> > ----- Original Message -----
> > From: "Hayes, David" <[EMAIL PROTECTED]>
> > To: "CF-Talk" <[EMAIL PROTECTED]>
> > Sent: Wednesday, March 28, 2001 6:47 PM
> > Subject: RE: T-SQL and single quote lists
> >
> >
> > > What results are you getting?
> > >
> > > -----Original Message-----
> > > From: Jeff Gombala [mailto:[EMAIL PROTECTED]]
> > > Sent: Wednesday, March 28, 2001 5:43 PM
> > > To: CF-Talk
> > > Subject: T-SQL and single quote lists
> > >
> > >
> > > Is it possible to pass a single quoted list into a SQL server stored
> > > procedure for use in an "IN" statement:
> > > see the following code...
> > >
> > > ***************
> > > PROCEDURE
> > > ***************
> > >
> > > CREATE PROCEDURE spMEPFundList
> > > @mepid varchar(3),
> > > @CustList varchar(400),
> > > @StandList varchar(400)
> > > AS
> > >
> > > Select tFund_name, tTicker, Cust=0
> > > from tblFunds
> > > where tTicker in (@StandList)
> > > UNION
> > > Select tFund_Name, tTicker, Cust=1
> > > from tblCustomFunds
> > > where tTicker in (@CustList)
> > > and txtMEP = @mepid
> > >
> > > ****************
> > > CF CODE
> > > ****************
> > >
> > > <cfset CustList = "'AFAF', 'AGAX'">
> > >
> > > <cfstoredproc procedure="insertUser" ...>
> > > <cfprocparam ....
> > > <cfprocparam type="IN" cfsqltype="CF_SQL_VARCHAR"
> > > dbvarname="@CustList" value="#PreserveSingleQuotes(request.CustList)#"
> > > null="no">
> > > <cfprocparam ....
> > > </cfstoredproc>
> > >
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
