SSRS - Report for Stored Procedure with Multiple Values Passed

  • Shawn Melton

    SSC-Insane

    Points: 24675

    Comments posted to this topic are about the item SSRS - Report for Stored Procedure with Multiple Values Passed

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • TheComedian

    SSCrazy

    Points: 2928

    Although this is a relatively simple topic compared to what I'm doing now; I can see how a beginner to SSRS would struggle with getting the report to accept multiple parameter values. This article does a good job of showing the necessary tricks to complete this task (notably the function that converts the parameter values into a single column table).

    Very practical topic (I get requests like this all the time) and well explained.

  • PHYData DBA

    SSCertifiable

    Points: 7541

    Nice simple article that covers ways to resolve what can be a very frustrating issue with SSRS.

    Many developers and DBA's alike have a difficult time understanding the difference between passing a Stored Procedure a single value through a parameter that only supports a single value and passing 20 values to parameter that supports that.

    It would be nice if you could use a single column Table Input parameter to do this instead of having to convert a nvarchar comma separated string to a table using functions.

    Wait a minute... You can do that!!! But it is a pain.

    Please see the information in these posts.

    http://geekswithblogs.net/GruffCode/archive/2012/06/13/using-table-valued-parameters-in-sql-server.aspx

    http://geekswithblogs.net/GruffCode/archive/2012/06/21/using-table-valued-parameters-with-sql-server-reporting-services.aspx

    https://connect.microsoft.com/SQLServer/feedback/details/355949/execute-permission-missing-on-user-defined-table-type

    It is and advanced method, but it is a lot easier if your have to use Stored Procedures.

    Of course this issue does not happen when you use a text query.

    Unless that needs to support string values that have spaces in a multi select list.

    If this is your thing see this Select post.

    insertStatements.AppendLine(String.Format("INSERT {0} VALUES ('{1}') ", variableName, paramValue))

  • g.britton

    SSChampion

    Points: 13689

    Since SSRS just passes a comma-delimited string, I write my query like this:

    select ...

    from ...

    where column in (@var)

    and either:

    1. put the base query in a view or TVF and add the where clause in SSRS, which substitutes the variable before passing it through to SQL.

    2. assemble the query as a string and execute it dynamically (in a proc).

    There's another option too (depends on the size of the row set):

    3. run the query without the parameters and filter it later in SSRS. Obviously not the best idea if the row set is large.

    Gerald Britton, MCSE-DP, MVPToronto PASS Chapter[/url]

  • PHYData DBA

    SSCertifiable

    Points: 7541

    g.britton (12/9/2014)


    Since SSRS just passes a comma-delimited string, I write my query like this:

    select ...

    from ...

    where column in (@var)

    and either:

    1. put the base query in a view or TVF and add the where clause in SSRS, which substitutes the variable before passing it through to SQL.

    2. assemble the query as a string and execute it dynamically (in a proc).

    There's another option too (depends on the size of the row set):

    3. run the query without the parameters and filter it later in SSRS. Obviously not the best idea if the row set is large.

    +1 on all of that.

  • dave87

    SSC Enthusiast

    Points: 117

    Remember to use NVARCHAR(x) in your stored proc and not VARCHAR. We had a situation where the stored proc was created to accept the string from ssrs as a varchar. It would return results only for the first item in the list. As soon as we changed that to nvarchar everything worked perfect.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply