How can I pass and process an array of varchars as parameters to/within a SQL Server stored procedure parameter?
<p>I use an existing stored procedure that declares these parameters:</p>

<pre><code>@Unit varchar(4000),
@BegDate datetime,
@EndDate datetime,
@SortBy varchar(20)
</code></pre>

<p>The challenge I now face is coming up with a derivation of that stored procedure which takes any number of @Unit parameters, from 1..N</p>

<p>How can I do that? Is it a matter of changing the @Unit parameter to @Unit varchar(max), assigning delimited values to the parameter, and then parsing the delimited-value from within the stored procedure?</p>

<p>IOW, do I need to do something like this in the stored proc:</p>

<pre><code>@Unit varchar(max),
@BegDate datetime,
@EndDate datetime,
@SortBy varchar(20)
</code></pre>

<p>...this in the C# code:</p>

<pre><code>string unit = unit1 +',' + unit2 +',' + unit3 // and on for however many units are being added
. . .
sqlCommand.Parameters.AddWithValue("@Unit", unit);
</code></pre>

<p>...and then, back in the stored proc, parse the comma-delimited values into a string array and have it query the table like so (pseudo-dml):</p>

<pre><code>string[] unitArray = @Unit.Split(',');
. . .
SELECT BLA, BLEE, BLOO FROM F_BUELLER WHERE Unit in unitArray[0], unitArray[1], unitArray[2], // and on for however many units are being queried for
</code></pre>

<p>...or what/how?</p>

<p>NOTE: If this were a "normal" project (non-SSRS, that is), I would simply use the original stored proc, calling it multiple times, and amalgamating the returned data, grouped by Unit. I don't know if that's possible or how to do it from SSRS, in an rdl/xml file, though...</p>

<h2>UPDATE</h2>

<p>What really needs to happen here is that there be a page break after each unique "Unit", the reason being that the report is exported to Excel, and the requester wants each Unit on its own separate sheet.</p>

<p>So basically I need to call the same stored procedure N times, plopping the results each time on its own "page."</p>

<p>So the tip <a href="http://forums.devshed.com/ms-sql-development-95/using-multiple-stored-procedures-report-sql-server-reporting-services-280259.html" rel="nofollow">here</a> seems to be good ("you can create multiple datasets in Reporting Services and then use them in different elements in the report. Just add a  from the Dataset: dropdown on the data tab")</p>

<p>The crux of the biscuit is, though: how can I provide 1, and only 1, dataset for each Unit? There is no way to know in advance how many units/sheets are going be generated/needed; there could be a couple, there could be a couple dozen.</p>