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

<p>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. </p>

<p>SQL Server 2005 is my only applicable limitation I think.</p>

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

Yeah, your current solution is prone to SQL injection attacks.
The best solution that I've found is to use a function that splits text into words (there are a few posted here, or you can use
this one from my blog
) and then join that to your table. Something like:
```
```
SELECT d.[Name]
FROM Department d
    JOIN dbo.SplitWords(@DepartmentIds) w ON w.Value = d.DepartmentId
```
```