December 9, 2014 at 12:09 am
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
December 9, 2014 at 4:15 am
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.
December 9, 2014 at 7:35 am
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.
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))
December 9, 2014 at 1:41 pm
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, Pluralsight courses
December 9, 2014 at 3:00 pm
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.
March 7, 2017 at 9:50 am
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply