How to setup passing list of multi-value parameter to stored proc based DataSet

Question

I have three datasets and three parameters

Parameters
@customer parameter <<<<this is filled with a stored procedure
@configid parmater <<<<this is filled with a sp_getconfigids(this SP takes a @customer as a parameter and returns configid's)
@detailrecord <<<this is filled with a sp_getdetails sp (this SP should take the @configid1, @configid2, @config3 as the arguement)

I have the first parameter and drop down working correctly so a report user can select a customer name from the list. I have the second drop down and parameter list being filled with cascading parameters so that the configid's are associated with that particular customer.

Then I present the list of configid's to the user, they will select the All Checkbox. So now I believe I get the @configids filled with @configid = value1, @configid = value2, @configid = value3.

I have this second parameter and selection working.

I am unable to figure out how to pass the @configid parameters to a third @etailrecord parameter and DataSet. How do I take the All selection from the @configid and pass it to the third DataSet?

Here is the SP that should provide back the detail rows.

select *

 

from customerconfigdetail a (nolock)
where configid IN (@configid)

Thanks very much for your help. You guys are great!
Eric

Answer 1

Hi Eric ,

    In order to address the scenario when the User selects the 'ALL' option in your dropdown for ConfigIds . Here are couple of ideas that you can try :


      1. First , fairly simple one try this in your sp [Sp_getdetails] it might work :

          Create Procedure Sp_getDetail
          (@ConfigId <SOMEDATATYPE> = NULL)
          AS
          BEGIN 
           ......

            if (@ConfigId is NULL)  -- So the Idea is to pass NULL when user selects 'ALL'
            SELECT * FROM CUSTOMERCONFIGDETAIL(NOLOCK) 
            else 
           SELECT * FROM CUSTOMERCONFIGDETAIL(NOLOCK) WHERE ConfigId in (@ConfigId)
           .......
          END

         2. If the Idea 1 doesn't work then try using the CASE Statement in the WHERE Condition : 

           Create Procedure Sp_getDetail
          (@ConfigId <SOMEDATATYPE> = NULL)
          AS
          BEGIN 
           ......
           SELECT * FROM CUSTOMERCONFIGDETAIL(NOLOCK) WHERE ConfigId in (CASE WHEN @ConfigId Is NULL THEN (SELECT DISTINCT ConfigId FROM CUSTOMERCONFIGDETAIL(NOLOCK)) ELSE @ConfigId END)
           .......
          END

          But here , pls note that you might have to replace this query in the CASE Statement : SELECT DISTINCT ConfigId FROM CUSTOMERCONFIGDETAIL(NOLOCK)  with the TSQL Query with which you populated your ConfigId dropdown originally and hence that portion that I have written is only an assumption to bring out all the available ConfigIds.


Also note in both the ideas I have allowed NULL for @ConfigId parameter . So try either 1 or 2 whichever fits your case. Let me know how it turned out .......

Cheers
Anand

                  if you feel this post has answered your question, pls feel free to mark it as 'answer'

         

 

 

 

Answer 2

I found this:

Writing Queries for Multivalue Report Parameters

You can define a multivalue parameter for any report parameter that you create. However, if you want to pass multiple parameter values back to a data source by using the query, the following requirements must be satisfied:

• The data source must be SQL Server, Oracle, Analysis Services, SAP BI NetWeaver, or Hyperion Essbase.
• The data source cannot be a stored procedure. Reporting Services does not support passing a multivalue parameter array to a stored procedure.
• The query must use an IN clause to specify the parameter.

SQL Server 2008 <<<<it doesn't look like SSRS supports passing a multivalue parameter to a Stored Procedure, WTF.

Thanks everyone for looking at this.

Eric

Answer 3

Hi Eric - Some details about the report parameter datatypes, the report/parameter datasets, the stored procedure parameters, and what errors you receive would help give a clearer answer. I assume you are getting datatype mismatch or TSQL syntax errors when you try it.

But in the simplest case, I created a:

- Stored Procedure:
    create procedure MultiValueParmProc @valuelist varchar(100)
    as
    select 'value received'=@valuelist   

- A report with a multi-value parameter called "valuelistparm" and set the available values to 1,2,3,4
- A dataset defined with a query type of "stored procedure" specifying MultiValueParmProc and mapping the @valuelist procedure parameter to the @valuelistparm report parameter
- A table mapped to the dataset and displays the "value_received" in the result set of the parameter

When I run it, I see the "1,2,3" value I selected in the report parameter being returned in the result set, so this demonstrates how RS builds them in a string list, and how you can pass them to a stored procedure. If the 'ALL' option is selected, it does not result in 'ALL' being set in the parameter, instead, the full list of available values is sent.

SQL Server's Transact SQL language for a long time did not support array or multi-value variables; a few releases ago we did add support for table variables for this purpose. Unfortunately, RS did not add support for this, probably due to the complexity of accurately declaring and passing table variables compatible with the procedure's table variable parameter. 

A more accurate explanation of the limitation would be "The SQL Server Transact SQL language only supports muti-value/array variables (and procedure parameters) in the form of table variables, and Reporting Services does not support passing multi-value report parameters as table-valued variables to dataset queries." 

The trick here is to workaround this limitation by passing the report parameters as strings and handling them as such in the procedure. My example above shows that you can send a list to a stored procedure, I understand that this is a lot simpler than your case, but it does give you something to build from.

I've blogged a few things that could help you here, primarily using EXEC() statements to build queries, and using SQL Profiler to see what is being passed to SQL from your report. I'd suggest you read through it, and the rest below will make more sense.

If these ultimately have to be int values in your procedure's query, what I'd suggest is:

- Define the mv report parameter as strings, define the procedure parameter as a string (as I did in my example), and then parse through it in TSQL to convert to INT values in a #temp table or @table variable that can be used with "SELECT...FROM....WHERE column in (SELECT column from @tablevariable)".

- Define the dataset to build the procedure's execute statement as a string with the param values, and run with EXEC()

- Define the procedure parameter as a string (like I did) and build the query in the procedure and execute your final SELECT with EXEC()

I can give more detailed code for each if you want.

Be aware of the issues with EXEC() and SQL Injection I mentioned in my blog, and please let us know how this works out for you.

Thanks, Dean

---

This posting is provided "AS IS" with no warranties, and confers no rights.