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

B. Clay Shannon

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.

supergrady

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

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

From Dev

How to pass two values to a single parameter in SQL Server stored procedure?

From Dev

How to pass schema as parameter to a stored procedure in sql server?

From Dev

How can I use the OUT parameter from a SQL Server stored procedure in a vb.net code

From Dev

How can I use the OUT parameter from a SQL Server stored procedure in a vb.net code

From Dev

How can I back up a stored procedure in SQL Server?

From Dev

How can I test stored procedure errors in SQL Server

From Dev

SQL Server 2008 Passing Multi-value Parameters or Parameter Array to a Stored Procedure

From Dev

Delphi - pass table valued parameter to SQL Server stored procedure

From Dev

Delphi - pass table valued parameter to SQL Server stored procedure

From Dev

How to pass Table-Valued parameters from java to sql server stored procedure?

From Dev

SQL Server Stored Procedure Parameter

From Dev

sql server stored procedure IN parameter

From Dev

SQL Server Stored Procedure Parameter

From Dev

How to pass column name as well as Table Name to a stored procedure as a parameter in SQL Server?

From Dev

How to pass dynamically created filters as parameter of stored procedure in SQL Server and filter data?

From Dev

How to pass dynamically created filters as parameter of stored procedure in SQL Server and filter data?

From Dev

How to pass SQL stored procedure NVARCHAR parameter with Hebrew?

From Dev

How to pass SQL stored procedure NVARCHAR parameter with Hebrew?

From Dev

Can you pass a series of parameters to a stored procedure

From Dev

How to call sql server stored procedure with input parameters in Qt

From Dev

How are parameters passed from SQL Server to a CLR based stored procedure?

From Dev

How to send and receive parameters to/from SQL Server stored procedure

From Dev

How to call sql server stored procedure with input parameters in Qt

From Dev

How to create dynamic parameters in SQL Server stored procedure

From Dev

SQL Server: How to use a database name as a parameter in a stored procedure?

From Dev

SQL Server: How to use a database name as a parameter in a stored procedure?

From Dev

How to pass Nullable Bit type value into to SQL Server stored procedure?

From Dev

How to pass rows of values into SQL Server stored procedure?

Related Related

  1. 1

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

  2. 2

    How to pass two values to a single parameter in SQL Server stored procedure?

  3. 3

    How to pass schema as parameter to a stored procedure in sql server?

  4. 4

    How can I use the OUT parameter from a SQL Server stored procedure in a vb.net code

  5. 5

    How can I use the OUT parameter from a SQL Server stored procedure in a vb.net code

  6. 6

    How can I back up a stored procedure in SQL Server?

  7. 7

    How can I test stored procedure errors in SQL Server

  8. 8

    SQL Server 2008 Passing Multi-value Parameters or Parameter Array to a Stored Procedure

  9. 9

    Delphi - pass table valued parameter to SQL Server stored procedure

  10. 10

    Delphi - pass table valued parameter to SQL Server stored procedure

  11. 11

    How to pass Table-Valued parameters from java to sql server stored procedure?

  12. 12

    SQL Server Stored Procedure Parameter

  13. 13

    sql server stored procedure IN parameter

  14. 14

    SQL Server Stored Procedure Parameter

  15. 15

    How to pass column name as well as Table Name to a stored procedure as a parameter in SQL Server?

  16. 16

    How to pass dynamically created filters as parameter of stored procedure in SQL Server and filter data?

  17. 17

    How to pass dynamically created filters as parameter of stored procedure in SQL Server and filter data?

  18. 18

    How to pass SQL stored procedure NVARCHAR parameter with Hebrew?

  19. 19

    How to pass SQL stored procedure NVARCHAR parameter with Hebrew?

  20. 20

    Can you pass a series of parameters to a stored procedure

  21. 21

    How to call sql server stored procedure with input parameters in Qt

  22. 22

    How are parameters passed from SQL Server to a CLR based stored procedure?

  23. 23

    How to send and receive parameters to/from SQL Server stored procedure

  24. 24

    How to call sql server stored procedure with input parameters in Qt

  25. 25

    How to create dynamic parameters in SQL Server stored procedure

  26. 26

    SQL Server: How to use a database name as a parameter in a stored procedure?

  27. 27

    SQL Server: How to use a database name as a parameter in a stored procedure?

  28. 28

    How to pass Nullable Bit type value into to SQL Server stored procedure?

  29. 29

    How to pass rows of values into SQL Server stored procedure?

HotTag

Archive