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

  • Article

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

Comments

  • Anonymous
    June 01, 2005
    If only SQL Server supported arrays, lists, or even allowed you to use its nice table variables as sproc inputs ...

    Anyway, here's a nice article on the various techniques for mimicking arrays and lists in T-SQL: http://www.sommarskog.se/arrays-in-sql.html

    The article covers several approaches, with several implementations of each approach (including procedure implementations for previous versions of SQL without UDF support), and ends with a performance evluation of each. What's nice is that it also covers the popular dynamic sql method (which is hugely insecure, and slower than all of the other methods to boot), which you can then point to when someone tries to tell you that dynamic sql is fast and you need to trade off security for speed.

    We use permutations of these ideas all over our SQL code, even going so far as to implement multi-dimensional array parsing (or as we like to think of it, "arrays of objects"), where the array entries can be more than just integers (strings, datetimes, bits, bigints, if you can represent the SQL type in a string we can parse it out).

    One thing I'd recommend you consider with your function is to use a version with text or ntext input so that you're not arbitrarily limiting your array size. At the very least, you can double your varchar input since varchars are 8-bit ANSI chars rather than 16-bit Unicode chars. No need for unicode in a function that only parses ints, but it's very useful to use nvarchar or ntext inputs for a function that parses strings. Keep in mind that if you do switch to a text blob, you'll need to chunk it out (being careful to always end chunks with a delimiter) to easily parse it.

  • Anonymous
    June 23, 2008
    PingBack from http://christophersite.freehostingz.com/sqlfunctionsbit.html

  • Anonymous
    June 19, 2009
    PingBack from http://edebtsettlementprogram.info/story.php?id=24461