views:

41

answers:

1

I have very simple query that calls a UDF which splits a field by comma. The query is

select top 10 * FROM Emails e WHERE EXISTS(SELECT TOP 1 1 FROM dbo.fn_Split(e.committees,','))

When I run/parse it, I get:

Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '.'.

I think it must have something to do with SQL 2000. If you switch out e.committees for something hardcoded (i.e., 'A,B,C,D') it works fine.

A: 

SQL 2000 doesn't support passing column names to TVFs. That was brought in in SQL2005 along with CROSS APPLY

I'm not really sure what you are needing to do here. Is e.committees a non 1NF list of numeric committee ids? If so

select top 10 <column-list>
FROM Emails e 
WHERE e.committees
like '%[0-9]%'
ORDER BY ...

Might work but a better solution would be to store these in a normalised form in a table with columns email_id,committee_id or whatever. This will likely make your queries easier!

Martin Smith

related questions