I ran into this some time ago. The following function will take your list
and a 1 character delimiter and return a table of the parsed results.
Originally when I did this we were on SQL7 so I do have a SP version if you
want it, but I prefer the function.
CREATE FUNCTION f_reuParseList (@tcList VARCHAR(8000),
@tcDelimiter CHAR(1))
RETURNS @ParsedList TABLE (ListID int IDENTITY,
ListItem varchar(8000))
AS
BEGIN
DECLARE @lcListWork varchar(8000),
@lnCommaPos int,
@lcItem varchar(8000)
SET @lcListWork = @tcList
WHILE LEN(@lcListWork) > 0
BEGIN
SET @lnCommaPos = CHARINDEX(@tcDelimiter, @lcListWork)
IF @lnCommaPos > 0
BEGIN
SET @lcItem = SUBSTRING(@lcListWork, 1,
@lnCommaPos - 1)
SET @lcListWork = SUBSTRING(@lcListWork,
@lnCommaPos + 1,
LEN(@lcListWork) - @lnCommaPos)
END
ELSE
BEGIN
SET @lcItem = @lcListWork
SET @lcListWork = ''
END
INSERT INTO @ParsedList
VALUES (@lcItem)
END
RETURN
END
GO
______________________________________________________
Bill Grover
Supervisor MIS Phone: 301.424.3300 x3324
EU Services, Inc. FAX: 301.424.3696
649 North Horners Lane E-Mail: [EMAIL PROTECTED]
Rockville, MD 20850-1299 WWW: http://www.euservices.com
______________________________________________________
> -----Original Message-----
> From: S. Isaac Dealey [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, November 19, 2002 1:52 PM
> To: CF-Talk
> Subject: Re: Stored Proc list input
>
>
> Likely you're going to run into a larger issue with your
> database not being
> able to interpret the @PassVariable value as an integer... at
> least with MS
> SQL Server I have yet to find a way to pass a list to a
> stored procedure
> without passing it as a varchar and then using exec
> (@mysqlstatement) or
> exec sp_executesql @mysqlstatement which somewhat defeats the
> purpose of the
> storedprocedure being pre-processed.
>
> > I need to pass a string such as 1,2 in to a stored procedure. The
> > variable
> > is then used like this:
>
> > Where MYVariable IN (@PassVariable)
>
> > Now the problem is that MYVariable is an INT and I am passing in the
> > @PassVariable as a string.
>
> > I get this error:
>
> > Syntax error converting the varchar value '1,2' to a column
> of data type
> > int.
>
> > I need to figure some way around this. Any ideas?
>
> > Thanks,
>
> > Neil
>
> S. Isaac Dealey
> Certified Advanced ColdFusion 5 Developer
>
> www.turnkey.to
> 954-776-0046
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
