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:
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.