User defined functions in SQL 2000

November 18, 2004

One of the more useful features of SQL 2000 is the ability to create a user defined function.  One great thing you can do, is build a table that is returned to the caller.  For example:

CREATE FUNCTION fn_Split (@sText varchar(8000), @sDelim varchar(20) = ‘ ‘)
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))

Can then be used as part of an IN clause like this:

WHERE somefield IN (SELECT value FROM fn_split( ‘x,y,z,a,b,c’, ‘,’ ))

That’s really useful.  One thing to remember: If you intend to use this function to compare to a field that has a collation defined, then the field returned by this function should use the same collation. Otherwise, you will receive an error about mismatched types.