SQL Server 2005 - Loop through/split a delimited string

[timeshifter]

I'm sure I'm not the only one who's had times where I need to insert several items into a table, but only one field is different between them. The scenario that led me to write this particular loop was a security system. One table contains a list of roles, another table contains user information, and a third table contains lists of what users have what role levels. A global admin needed open access to everything, so they'd need one entry in the UserRoles table per role definition. For me, this came to about 12 entries. And I don't care if it's an intranet application, I am NOT writing an app that will make 12 database hits to accomplish one task. The solution? Send SQL a comma-separated list and loop through it, breaking off only what I need to insert on that particular iteration.

Naturally, I used Google looking for code, but it all seemed too complicated. Most of them tried to preserve the original list, which is all but useless once the function is done. They also held on to two index variables, one for the start of the string and one for the end, based on where the delimiter is. I thought, "well, that's kinda dumb...", so I set to work on this, which is much smaller, cleaner, and accomplishes the same thing.

Basically, it has three variables. @strRoles is a comma-separated list that is defined as a parameter. @pos is an int used to mark the location of the next delimiter, and @RoleID is used to store the extracted piece of information.

This function absolutely relies on a delimiter after every data piece, so the first check is to make sure it's there. Pretty simple:

IF substring(@strRoles, LEN(@strRoles)-1,1)<>','
        SET @strRoles = @strRoles + ',' --add a comma to the end if it isn't there

Next, I set the @pos variable and declare my result. In this example, the data pieces are all ints, but if you need to pull out strings, you'd use varchar() instead.

SET @pos=0
    DECLARE @RoleID as int

Now comes the loop itself. The loop is checking to make sure the delimiter is still there, namely a comma. The first thing it does is set our temp variable, @RoleID, equal to the substring of the data string to the first index of the delimiter. No mess there. Then it inserts into the table I need it to, where @UserID is also a defined parameter. The last thing it does is reassign the data string to NOT include the item it just inserted, basically chopping off the first item, and the following delimiter.

WHILE charindex(',',@strRoles)>0
    BEGIN
        SET @RoleID = cast(substring(@strRoles,0, charindex(',',@strRoles)) as int)
        INSERT INTO [tblUserRoles] ([UserID], [RoleID]) VALUES (@UserID, @RoleID)
        SET @strRoles = substring(@strRoles, charindex(',',@strRoles)+1, LEN(@strRoles) - @pos) --remove the first item from the list
    END

If I passed it "1,4,12,8" as the @strRoles parameter, this is what it looks like after each pass through the loop:

parameter     >> "1,4,12,8"
add delimiter >> "1,4,12,8,"
first pass    >> "4,12,8,"
second pass   >> "12,8,"
third pass    >> "8,"
fourth pass   >> "" , WHILE is no longer met, loop ends

I hope this saves somebody the trouble that I've gone through to find a reliable split operation in T-SQL!