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
