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