Jeff,
I believe SQL Server would interpret the argument as a single VARCHAR value,
not a list. However, you could tokenize the string in your procedure and
then do a subselect in the IN clause as follows:
CREATE PROCEDURE spMEPFundList (
@mepid varchar(3),
@CustList varchar(400),
@StandList varchar(400)
)
AS
SET NOCOUNT ON
DECLARE @list VARCHAR(400) -- list holder
DECLARE @val VARCHAR(64) -- temp value
DECLARE @pos INT -- current comma
position
DECLARE @which VARCHAR(5) -- execution
status
CREATE TABLE #ttbl_vals (val VARCHAR(64), which VARCHAR(5)) -- temp values
SET @list = @CustList
SET @which = 'cust'
list_processor:
SET @pos = CHARINDEX(',', @list)
WHILE @pos <> 0
BEGIN
SET @val = LEFT(@list, @pos - 1)
SET @list = RIGHT(@list, LEN(@list) - CHARINDEX(',', @list))
SET @pos = CHARINDEX(',', @list)
INSERT INTO #ttbl_vals (val, which) VALUES (@val, @which)
END
IF LEN(@list) > 0
INSERT INTO #ttbl_vals (val, which) VALUES (@list, @which)
IF @which = 'cust'
BEGIN
SET @list = @StandList
SET @which = 'stand'
GOTO list_processor
END
SELECT tFund_name, tTicker, Cust=0
FROM tblFunds
WHERE tTicker IN (SELECT val
FROM #ttbl_vals
WHERE which = 'stand')
UNION
SELECT tFund_Name, tTicker, Cust=1
FROM tblCustomFunds
WHERE tTicker in (SELECT val
FROM #ttbl_vals
WHERE which = 'cust')
AND txtMEP = @mepid
DROP TABLE #ttbl_vals
HTH,
Seva Petrov
> -----Original Message-----
> From: Jeff Gombala [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, March 28, 2001 6: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
