A SQL function you just CAN’T live without... (posted by Aaron)

When SQL functions were first introduced I stumbled across one function in particular that I’ve probably used more than any other.  In fact, I doubt I’ve developed too many databases that didn’t use this great little function.  It’s really quite simple… but very, very powerful.  It’s called… fn_ParseCommaDelimitedList.  This simple yet powerful little function allows you to pass in a comma delimited list of integers for use when querying a subset of rows from a table. 

Example:

You’ve got a table of employees and a simple list of ids of a subset of those employees.  You need to return just the employee information of the subset of employees.

DECLARE @IDList varchar(2000)
SET @IDList = ‘1,2,3,4,5,6,7,8,9,10’

SELECT e.*
FROM Employee e
JOIN dbo.fn_ParseCommaDelimitedList(@IDList) ids ON (e.EmployeeID = ids.ItemID)

So simple.  So easy.  So darn convenient.

ALTER FUNCTION dbo.fn_ParseCommaDelimitedList
(
@CommaDelimitedList varchar(4000)
)
RETURNS @TableVar TABLE (ItemID int NOT NULL )
AS
BEGIN
                DECLARE @IDListPosition int
DECLARE @IDList varchar(4000)
DECLARE @ArrValue varchar(4000)
SET @IDList = COALESCE(@CommaDelimitedList, '')
IF @IDList <> ''
BEGIN
                -- Add comma to end of list so user doesn't have to
                SET @IDList = @IDList + ','
                -- Loop through the comma demlimted string list
                WHILE PATINDEX('%,%' , @IDList ) <> 0
BEGIN
SELECT @IDListPosition = PATINDEX('%,%' , @IDList)
SELECT @ArrValue = LEFT(@IDList, @IDListPosition - 1)
                                -- Insert parsed ID into TableVar for "where in select"
                                INSERT INTO @TableVar (ItemID) VALUES (CONVERT(int, @ArrValue))
                                -- Remove processed string
SELECT @IDList = STUFF(@IDList, 1, @IDListPosition, '')
END
END
RETURN

END

Put it to use.  Anyone else have a function they just can't live without?

Aaron Bjork