Even further off topic... why in the heck does a varchar have a comma
delimited list in it? That violates the first degree of normalization.
Also, why do you need to split it for an in clause? If the field
data is wrapped in parenthesis, shouldn't it work as is?
-dhs
Dean H. Saxe, CISSP, CEH
[EMAIL PROTECTED]
"What difference does it make to the dead, the orphans, and the
homeless, whether the mad destruction is wrought under the name of
totalitarianism or the holy name of liberty and democracy? "
--Gandhi
On Jun 5, 2007, at 1:36 PM, Paul Morton wrote:
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
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 FusionLink
-------------------------------------------------------------
