T-SQL stored procedure that accepts multiple Id values

Question

Is there a graceful way to handle passing a list of ids as a parameter to a stored procedure?

For instance, I want departments 1, 2, 5, 7, 20 returned by my stored procedure. In the past, I have passed in a comma delimited list of ids, like the below code, but feel really dirty doing it.

SQL Server 2005 is my only applicable limitation I think.

create procedure getDepartments
  @DepartmentIds varchar(max)
as
  declare @Sql varchar(max)     
  select @Sql = 'select [Name] from Department where DepartmentId in (' + @DepartmentIds + ')'
  exec(@Sql)

Answer 1

Erland Sommarskog has maintained the authoritative answer to this question for the last 16 years: Arrays and Lists in SQL Server.

There are at least a dozen ways to pass an array or list to a query; each has their own unique pros and cons.

• Table-Valued Parameters. SQL Server 2008 and higher only, and probably the closest to a universal "best" approach.
• The Iterative Method. Pass a delimited string and loop through it.
• Using the CLR. SQL Server 2005 and higher from .NET languages only.
• XML. Very good for inserting many rows; may be overkill for SELECTs.
• Table of Numbers. Higher performance/complexity than simple iterative method.
• Fixed-length Elements. Fixed length improves speed over the delimited string
• Function of Numbers. Variations of Table of Numbers and fixed-length where the number are generated in a function rather than taken from a table.
• Recursive Common Table Expression (CTE). SQL Server 2005 and higher, still not too complex and higher performance than iterative method.
• Dynamic SQL. Can be slow and has security implications.
• Passing the List as Many Parameters. Tedious and error prone, but simple.
• Really Slow Methods. Methods that uses charindex, patindex or LIKE.

I really can't recommend enough to read the article to learn about the tradeoffs among all these options.