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

Multi-valued parameter with In clause in SP Expand / Collapse
Author
Message
Posted Monday, July 7, 2014 6:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 11:25 AM
Points: 21, Visits: 65
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!
Post #1590166
Posted Monday, July 7, 2014 6:39 PM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:15 AM
Points: 6,172, Visits: 7,247
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1590167
Posted Monday, July 7, 2014 6:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 11:25 AM
Points: 21, Visits: 65
Thank you so much for pointing it out! Craig
I'll try and let you know!
BTW I like your motto too!
Post #1590169
Posted Tuesday, July 8, 2014 4:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 11:25 AM
Points: 21, Visits: 65
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,','))
Post #1590556
Posted Tuesday, July 8, 2014 4:44 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 3,374, Visits: 7,301
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1590557
Posted Tuesday, July 8, 2014 5:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 11:25 AM
Points: 21, Visits: 65
Thank you for the reminder Luis!
Will do so now!
Post #1590564
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse