I got this SQL UDF off of the list a long time ago. I believe it will
do what you need.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/****** Object: User Defined Function dbo.fnc_ParseDelimited
Script Date: 1/12/2005 3:18:53 PM ******/
CREATE FUNCTION dbo.fnc_ParseDelimited
(
@delimitedList VarChar(8000)
)
RETURNS @tblSample TABLE (CounterID Int, FieldValue Varchar(100))
BEGIN
Declare @CounterID VarChar(4)
Declare @FieldValue Varchar(100)
Declare @tmpTable Table (CounterID Int Primary Key, FieldValue
VarChar(100))
Set @CounterID = 1
While CharIndex(',', @delimitedList) > 0
Begin
Set @FieldValue =
LTrim(RTrim(subString(@delimitedList, 1, charIndex(',',
@delimitedList)-1)))
Insert Into @tmpTable Select @CounterID, @FieldValue
Set @delimitedList =
LTrim(RTrim(subString(@delimitedList, (charIndex(',', @delimitedList) +
1),
Len(@delimitedList))))
Set @CounterID = @CounterID + 1
End
If LTrim(RTrim(@delimitedList)) != ''
Insert Into @tmpTable Select @CounterID, @delimitedList
Insert Into @tblSample Select * From @tmpTable
Return
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
On 3/17/06, Ali Awan <[EMAIL PROTECTED]> wrote:
> Thanks Qasim,
>
> that almost helps, but my ID's are 6 character varchars.
> So what I'm passing, is all delimited by single quotes.
> My first problem is that from coldfusion when I use the cfstoredproc tag, how
> do I pass the parameter in, because it doesn't take a comma-separated
> character list as a valid parameter.
>
> Ali
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235659
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
