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


 


Comments (3)
  1. Todd Ostermeier says:

    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.

Comments are closed.

Skip to main content