SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Passing multi-valued parameter selections in to stored procedures


Passing multi-valued parameter selections in to stored procedures

Author
Message
Griffster
Griffster
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1795 Visits: 713
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.
Mauve
Mauve
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3422 Visits: 2066
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.
Jeremy-475548
Jeremy-475548
Right there with Babe
Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)Right there with Babe (771 reputation)

Group: General Forum Members
Points: 771 Visits: 906
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.
Griffster
Griffster
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1795 Visits: 713
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.
Mauve
Mauve
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3422 Visits: 2066
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.
Arul Paulus-Rosenow
Arul Paulus-Rosenow
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 141
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?
Mauve
Mauve
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3422 Visits: 2066
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.
Arul Paulus-Rosenow
Arul Paulus-Rosenow
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 141
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?
agron.bb
agron.bb
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 83
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
Camilo Torres
Camilo Torres
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 45
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...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search