How can I pass and process an array of varchars as parameters to/within a SQL Server stored procedure parameter?

Question

I use an existing stored procedure that declares these parameters:

@Unit varchar(4000),
@BegDate datetime,
@EndDate datetime,
@SortBy varchar(20)

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

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?

IOW, do I need to do something like this in the stored proc:

@Unit varchar(max),
@BegDate datetime,
@EndDate datetime,
@SortBy varchar(20)

...this in the C# code:

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

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

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

...or what/how?

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

UPDATE

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.

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

So the tip here 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")

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.

score 1 · Answer 1 · answered May 26 '16 at 23:26

You can make use of SQL Server Table Type Parameter.

Create a Type first in SQL Server

CREATE TYPE [dbo].[TVP_Units] 
AS TABLE(
 [Units] VARCHAR(1000)
)
GO

Make Your Stored procedure accept a variable of that type instead of a Varchar(1000) @Units variable.

ALTER PROCEDURE MyProc 
  @TVP_Units [dbo].[TVP_Units] READONLY
, @BegDate datetime
, @EndDate datetime
, @SortBy varchar(20)
....... rest of the code

I am not an expect in C# but your C# code would look something like ....

using (con)
{                

 //Create a DataTable ....

    DataTable _dt = new DataTable("Units");
    _dt.Columns.Add("Unit", typeof(string));
    _dt.Rows.Add("Unit 1");
    _dt.Rows.Add("Unit 2");
    _dt.Rows.Add("Unit 3");

    SqlCommand sqlCmd = new SqlCommand("dbo.MyProc", con);
    sqlCmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvpParam = sqlCmd.Parameters.AddWithValue("@TVP_Units", _dt); 
    //tells ado.net it is a TVP
    tvpParam.SqlDbType = SqlDbType.Structured; 

    sqlCmd.Parameters.AddWithValue("@BegDate", YourC#Param);
    sqlCmd.Parameters.AddWithValue("@EndDate", YourC#Param);
    sqlCmd.Parameters.AddWithValue("@SortBy", YourC#Param);
    con.Open();
    sqlCmd.ExecuteNonQuery();
 }

I wish it was C#, which I am comfortable with (also not really an expert), but I need to do this in SSRS / the rdl file. — B. Clay Shannon-B. Crow Raven, May 26 '16 at 23:30
99% sure SSRS won't accept a user-defined table-valued type parameter. At least I couldn't ever get it to work. — supergrady, May 27 '16 at 02:10

score 0 · Accepted Answer · answered May 27 '16 at 02:44

The way I've done this is by passing in a delimited string as a report parameter.

Build a dataset in the report which shreds the delimited string into rows using XML casting. Assuming that the @Unit parameter is comma-delimited when there is more than 1 value, the dataset query would look something like this:

DECLARE @UnitXml XML = CAST('<unit>' + REPLACE(@Unit,',','</unit><unit>') + '</unit>' AS XML)

SELECT unit.x.value('.','VARCHAR(200)') AS [unit]
FROM @UnitXml.nodes('/unit') AS unit(x)

Then make a tablix that has a row for each item in the dataset.

Put a subreport in the row group of the tablix. In your case, the subreport would point to a report part showing all the info for one Unit, and passing the unit number from the dataset to your stored procedure.

The stored procedure would run once for each Unit. I realize this is neither elegant nor efficient but this is SSRS.

I am a bit confused by this because (a) someone downvoted it (I upvoted it to get it back to 0); I don't know if the downvote was valid or not - if so, what's wrong with this approach? (b) I'm only an occasional dabbler (on an as-needed basis) in SSRS, so would need more specifics to make heads or tails of this answer - at this point, it seems almost like "voodoo programming" to me. — B. Clay Shannon-B. Crow Raven, May 27 '16 at 15:08
This post explains adding subreports: http://stackoverflow.com/questions/24744728/nested-sub-report-in-tablix-ssrs — supergrady, May 27 '16 at 22:11

How can I pass and process an array of varchars as parameters to/within a SQL Server stored procedure parameter?

UPDATE

2 Answers2