July 7, 2014 at 6:33 pm
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!
July 7, 2014 at 6:39 pm
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
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
July 7, 2014 at 6:55 pm
Thank you so much for pointing it out! Craig
I'll try and let you know!
BTW I like your motto too!
July 8, 2014 at 4:22 pm
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,','))
July 8, 2014 at 4:44 pm
I assume that you didn't read the article proposed by Craig. Read it and find out a way to improve the performance.
July 8, 2014 at 5:17 pm
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