Brian Sherwin's Blog

During my presentation on "Avoiding Database Entropy" I did a demo of a function to parse a delimited string and return it as a table.  This is extremely helpful when using multi-select controls in your applications.  In the end we still call the stored procedure like this:

GetUsersByID '1,2,7,9'

Most people make their stored procedure like this:

create proc GetUsersByID (@delimitedIDs varchar(200))
as
declare @SQL varchar(2000)
set @SQL = 'SELECT  Employee.EmployeeID, Employee.FirstName, Employee.LastName,
        Employee.MiddleName
FROM    Employee
WHERE EmployeeID in (' + @delimitedIDs + ')'
sp_executesql (@SQL)

Notice all that "RED SQL" -- left over from the Cold War I'm sure.

With a little creativity, we can improve performance, maintenance and clarity by doing it like this:

create proc GetUsersByID (@delimitedIDs varchar(200))
as
SELECT  Employee.EmployeeID, Employee.FirstName, Employee.LastName,
        Employee.MiddleName
FROM    Employee 
INNER JOIN dbo.ParamParserFn(@delimitedIDs, ',') tblID
ON Employee.EmployeeID = tblID.Id

Notice the subtle difference?  There's no dynamic SQL.  It's all good!  It will perform better too.

So here is the parsing function (note: be sure you qualify it correctly when you go to use it--SQL will like you better...wink, wink):

CREATE FUNCTION ParamParserFn( @delimString varchar(255), @delim char(1)) 
RETURNS @paramtable 
TABLE ( Id int ) 
AS BEGIN

DECLARE @len int,
        @index int,
        @nextindex int

SET @len = DATALENGTH(@delimString)
SET @index = 0
SET @nextindex = 0


WHILE (@len > @index )
BEGIN

SET @nextindex = CHARINDEX(@delim, @delimString, @index)

if (@nextindex = 0 ) SET @nextindex = @len + 2

 INSERT @paramtable
 SELECT SUBSTRING( @delimString, @index, @nextindex - @index )


SET @index = @nextindex + 1

END
 RETURN
END
GO

Your mileage may vary as you may need strings delimited and not just integers, but this should get you going down the right path.  I don't claim this as my own code, but I've been using this method for so many years, I can't remember where I first learned the idea.