I apologize for the OT post, but I'm getting frustrated.
I am attempting to use a UDF to split a varchar (comma delimited list of
ints) in an IN clause in a SQL statement, and I'm not able to get the SQL
statement to pass a Table field to the UDF.
I'm hoping someone sees a glaring error, or can tell me I can't do this.
MS SQL 2000
________________________________________________________________
CREATE FUNCTION dbo.Split
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List =
Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END
____________________________________________________________________________
____
SQL Statement
SELECT *
FROM dbo.ORDER_ITEMS oi LEFT OUTER JOIN
dbo.AP_translation tr ON tr.option_id IN
(select convert(int,Value) from dbo.Split(oi.OptionIDs, ','))
WHERE (oi.Order_ID = 12387)
____________________________________________________________________________
_
Error received:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '.'.
OptionIDs is a varchar(255) field with a comma delimited list of integers.
It doesn't like the oi.OptionIDs. If I put in a list like '0,1,2' in it's
place, the query works.
Can I not pass a field into a UDF?
Is there another way to skin this cat?
Paul
-------------------------------------------------------------
Annual Sponsor FigLeaf Software - http://www.figleaf.com
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by http://www.fusionlink.com
-------------------------------------------------------------
