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.