Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Passing multi-valued parameter selections in to stored procedures Expand / Collapse
Author
Message
Posted Thursday, January 17, 2008 5:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 5:05 AM
Points: 247, Visits: 696
I've followed many blogs and forums now on how to pass the values from a multi-value parameter in reporting services to a stored procedure, and, although I think I understand the concept I can't seem to get it to work. I've created a split function to separate out the selected values in to a table that can be referenced in an 'IN' clause in the stored procedure as below:

CREATE PROCEDURE [dbo].[spStoredProc]

@client char(6)
AS
BEGIN

SET NOCOUNT ON;
select d.* from table1 d
where d.clnt_no in (SELECT Item FROM dbo.Split(@client, ','))
END

and placed

=Join(Parameters!client.Value,", " )

in the value field for the parameter drop-down in the Dataset Parameters tab for the stored proc (command type: Stored procedure) in my RS report but it only ever seems to report back the very first selection in the parameter drop down, which suggests it isn't firing all the values to the in statement in the stored procedure.

Can someone suggest where I'm going wrong here please? I don't think it's the function but may be wrong.
Post #444261
Posted Thursday, January 17, 2008 3:04 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:27 AM
Points: 1,186, Visits: 1,973
Take a look at your split function and ensure that it is matching what you are passing in.

Per your "Join" statement you are putting a blank after the comma. E.g., the resulting string is "A, B, C" and not "A,B,C".

So this could be affecting your "IN" list matching.

Test out (unit test) your stored procedure independent of SSRS to ensure that if you pass the right delimited values you get the correct results.

The input parameter should also be coded as VARCHAR and not CHAR as CHAR will blank pad. I also think that 6 characters is a bit small to handle a comma delimited string of values. What are the lengths of the values and how many can be passed?

I suggest that the parameter should be defined as VARCHAR(8000) to handle an [almost] open-ended list of values.



(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Post #444631
Posted Thursday, January 17, 2008 4:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 8:24 AM
Points: 169, Visits: 820
I'd agree with everything above. Also, run the profiler to get the exact string that RS is passing in, so you can test with something real.
Post #444675
Posted Friday, January 18, 2008 4:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 5:05 AM
Points: 247, Visits: 696
Thanks both for the suggestions. Having tested with values in the split function I had it produced the values I was expecting. The key was the varchar v char data length. I'd originally written the stored procedure to expect one value as the field was 6 characters in length. When it was converted to take multi valued parameters I forgot to change that. The split function worked fine. Sometimes you need someone else to look at your code before you see the obvious! At least I know how to do this now until Microsoft make this a little more user-friendly from within ssrs.
Post #444787
Posted Monday, January 21, 2008 9:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:27 AM
Points: 1,186, Visits: 1,973
Update (JohnG): It appears that the posting (excerpt in the quoted text below) that I reference in my reply has been removed by the original poster after reading my response.

where (charindex(d.clnt_no,@client, 1)>0)


I disagree.
For starters, because you are using a function on the search column (clnt_no) in the WHERE clause, you will be negating any index usage on this column. Try this with a large table and see the query plan.

Additionally, depending upon the list of values that are passed in the parameter in conjunction with the values that are in the table, you could get a lot of "false positives" due to the character matching behavior of CHARINDEX. E.g., given clint_no values of 100 and 1000, a WHERE clause of: CHARINDEX(CAST(d.clnt_no AS VARCHAR), '1000', 1) > 0 will incorrectly return both 100 and 1000!



(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Post #445543
Posted Monday, March 17, 2008 11:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 12:46 PM
Points: 9, Visits: 77
Is it possible to control a parameter type as being single vs. multi-valued based on the value selected in the previous parameter? For example in parameter one the values are: City, State. In Parameter two the values are the respective list of cities or states.

If city is selected in parameter one then the user should only be able to select one city from parameter two. If State is selected in parameter one I want parameter two to be a mult-select.

If this is possible, could it be done via the XML code or would it be possible through any Visual Basic coding?
Post #470365
Posted Monday, March 17, 2008 11:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:27 AM
Points: 1,186, Visits: 1,973
I would create two different secondary parameters, populated by the appropriate queries, and then show/hide the appropriate parameter based upon the selection of the first parameter.


(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Post #470371
Posted Monday, March 17, 2008 11:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 12:46 PM
Points: 9, Visits: 77
Is this the same idea of a Cascading Parameter? If not how do you hide or make available a parameter. Under report properties if I check 'Hidden', then parameter three will not be visible if I select 'State' from parameter one. If the parameter becomes unavailable then I will not pass a value for that respective parameter to the SQL stored procedure, correct?
Post #470391
Posted Friday, September 3, 2010 7:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 1, 2014 1:12 AM
Points: 45, Visits: 78
Hi Experts,
I have a similar problem.. I got a table with values, and I need to update second table through stored procedure, because in procedure are done some calculation... How to perform that? Any example would be very useful for me.
Thanking all you in advanced and best regards
Post #980191
Posted Tuesday, April 3, 2012 9:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:08 AM
Points: 102, Visits: 37
I found myself in the same situation and after reviewing many posts couldn't find a simple solution. Then by light bulb turned on.
This is what I did
1) I set on of the parameters of my SP for my dataset as:
="SELECT '"+JOIN(Parameters!MultiValue1.Value,"' UNION SELECT '") + "'"
This works for 1,2,...N values selected and it creates a string like this:
SELECT 'Value1' UNION
SELECT 'Value2'
.
.
.
2) In my SP i placed this code
CREATE TABLE #Tbl_MultiValues(Value_ VARCHAR(100))
DECLARE @SQLStr VARCHAR(8000)

SET @SQLStr = 'INSERT INTO #Tbl_MultiValues' + @Value_

EXECUTE (@SQLStr);

Then in my WHERE clause
WHERE TableValue IN(SELECT * FROM #Tbl_MultiValues)

I hope you find this trick usefull...
Post #1277396
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse