Passing multi-valued parameter selections in to stored procedures

  • 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.

  • 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.


    [font="Arial Narrow"](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.[/font]

  • 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.

  • 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.

  • 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!


    [font="Arial Narrow"](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.[/font]

  • 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?

  • 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.


    [font="Arial Narrow"](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.[/font]

  • 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?

  • 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

  • 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...

Viewing 10 posts - 1 through 9 (of 9 total)

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