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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
