Multi-valued parameter with In clause in SP

  • Dear all:

    I have a question about how to configure the multi-valued parameter.

    Here's a sample query:

    Create Proc sp_city

    @CityID varcha(100)

    as

    Begin

    select City_name

    Where City_ID in (@CityID)

    End

    Go

    In SSRS my report will call this SP and the parameter @CityID will be automtically generated

    Right click on @CityID and click on 'allow multiple value' and I want the parameter to act as an user_input parameter so no default value or available value will be defined.

    Then I hit preview report the @CityID will show up, in the field I type in 12 then one record will be retrieved and displayed. If I input multiple-values as comma-separated value: 12,14,18 and view the report again nothing was shown not even error msg.

    I did some googling and someone suggested to change the SP as following:

    Create Proc sp_city

    @CityID varcha(100)

    as

    Begin

    select City_name

    Where City_ID in (select * from dbo.Split(@CityID))

    End

    Go

    But in SSRS it throws an error saying that invalid object dbo.Split(@CityID)

    Could you please let me know what is the right way to configure this type of the multi-valued parameter?

    Thank you so much!

  • dbo.Split is not a built in function. They're referencing building a function to do that, such as the following:

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

    What you basically do is turn the comma delimited string into a temp table, and join against it as your IN clause. I typically write these as:

    SELECT

    fieldlist

    FROM

    table

    JOIN

    splitter(@parameter) AS s

    ON table.field = s.field


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Thank you so much for pointing it out! Craig

    I'll try and let you know!

    BTW I like your motto too!

  • For everyone else's info.

    Here's my whole solution:

    CREATE FUNCTION [dbo].[FnSplit]

    (@List nvarchar(2000),@SplitOn nvarchar(5))

    RETURNS @RtnValue table

    (Id int identity(1,1),Value nvarchar(100))

    AS

    BEGIN

    While (Charindex(@SplitOn,@List)>0)

    Begin

    Insert Into @RtnValue (value)

    Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

    Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))

    End

    Insert Into @RtnValue (Value)

    Select Value = ltrim(rtrim(@List))

    Return

    END

    GO

    Select city

    from Table

    where ctiyID in (select value from dbo.Fnsplit(@CityId,','))

  • I assume that you didn't read the article proposed by Craig. Read it and find out a way to improve the performance.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thank you for the reminder Luis!

    Will do so now!

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

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