Maybe I missed something, but why wouldn't you just use a cfqueryparam with a type of sql_int and list="yes".
----- Original Message ---- From: Paul Morton <[EMAIL PROTECTED]> To: [email protected] Sent: Tuesday, June 5, 2007 1:36:40 PM Subject: [ACFUG Discuss] OT: SQL help Message 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 ------------------------------------------------------------- ------------------------------------------------------------- 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 -------------------------------------------------------------
