June 13, 2012 at 12:49 pm
My function for split parameter:
ALTER FUNCTION [dbo].[SplitParameterValues]
(@input nvarchar(4000), @row_delimiter char(1))
RETURNS @OutputTable TABLE ( [value] nvarchar(100))
AS
BEGIN
declare
@str1 nvarchar(100), @tmpstr nvarchar(4000)
WHILE LEN(@input) > 0
BEGIN
set @tmpstr = LEFT(@input, ISNULL(NULLIF(CHARINDEX(@row_delimiter, @input) - 1, -1), LEN(@input)))
set @input = SUBSTRING(@input,ISNULL(NULLIF(CHARINDEX(@row_delimiter, @input), 0), LEN(@input)) + 1, LEN(@input))
INSERT INTO @OutputTable ( [value] )
VALUES ( RTrim(LTrim(@tmpstr)))
END
RETURN
END
--------------------------it is not working-------
it woerks for this:team
declare @team varchar(max)
Set @team ='Pegasus Texas Construction, LP'
select * from dbo.SplitParameterValues(@Team,'|')
it wont work if team is:'A,C' and cant pass it as 'A|C'
as the report will be passing team as 'A,C,D,F'.....reports is not showing any records while I have records.
Can anyone tell what to change here???
June 13, 2012 at 1:12 pm
Looks like it works fine as long as you use the proper delimiter in the function call:
declare @team varchar(max)
set @team = 'a,b,c,d'
select * from dbo.SplitParameterValues(@Team,',')
Gives me:
value
a
b
c
d
June 13, 2012 at 1:14 pm
komal145 (6/13/2012)
My function for split parameter:ALTER FUNCTION [dbo].[SplitParameterValues]
(@input nvarchar(4000), @row_delimiter char(1))
RETURNS @OutputTable TABLE ( [value] nvarchar(100))
AS
BEGIN
declare
@str1 nvarchar(100), @tmpstr nvarchar(4000)
WHILE LEN(@input) > 0
BEGIN
set @tmpstr = LEFT(@input, ISNULL(NULLIF(CHARINDEX(@row_delimiter, @input) - 1, -1), LEN(@input)))
set @input = SUBSTRING(@input,ISNULL(NULLIF(CHARINDEX(@row_delimiter, @input), 0), LEN(@input)) + 1, LEN(@input))
INSERT INTO @OutputTable ( [value] )
VALUES ( RTrim(LTrim(@tmpstr)))
END
RETURN
END
--------------------------it is not working-------
it woerks for this:team
declare @team varchar(max)
Set @team ='Pegasus Texas Construction, LP'
select * from dbo.SplitParameterValues(@Team,'|')
it wont work if team is:'A,C' and cant pass it as 'A|C'
as the report will be passing team as 'A,C,D,F'.....reports is not showing any records while I have records.
Can anyone tell what to change here???
declare @team varchar(max)
Set @team ='Pegasus Texas Construction, LP'
select * from dbo.SplitParameterValues(@Team,'|')
Yes this would work as no split is required.
Why do you not just change the splitting character you are passing ie:
declare @team varchar(max) = 'A,B,C,D'
select * from dbo.SplitParameterValues(@team,',')
Fitz
June 13, 2012 at 1:33 pm
I have team name something like below
'Pegasus Texas Construction, LP'( which is one word)
declare @team varchar(max)
Set @team ='Pegasus Texas Construction, LP'
so i cannot take
(select * from dbo.SplitParameterValues(@Team,','))
June 13, 2012 at 1:38 pm
komal145 (6/13/2012)
I have team name something like below'Pegasus Texas Construction, LP'( which is one word)
declare @team varchar(max)
Set @team ='Pegasus Texas Construction, LP'
so i cannot take
(select * from dbo.SplitParameterValues(@Team,','))
So sometimes you want to split on (,) and sometimes not. How do you tell the difference?
Fitz
June 13, 2012 at 2:13 pm
ya..that the reason I took (SELECT * FROM dbo.SplitParameterValues(@Team,'|')))
But while filtering my report , in drop down it is passing value like'A,C,D,.......'
which is not working.
June 13, 2012 at 3:13 pm
It will split the string on whatever delimiter you pass into the function. If you pass in '|' and there is no '|' in the data, it won't split anything and will just return the whole string. If all your strings are delimited by different things, you'll have to come up with some way to pass in the correct delimiter to the function or else this will never work.
June 14, 2012 at 7:06 am
you can try,
(select * from dbo.SplitParameterValues(@Team,CHR(255)))
then in the tablix properties of your report> go to dataset properties> parameters
> add
= Join(Parameter!team.value,char(255)))
June 14, 2012 at 8:01 am
Take a look at the article in my signature about splitting strings. The version Jeff wrote will blow the doors of the while loop style splitter. The article goes in depth about performance and how to use the version Jeff wrote.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 14, 2012 at 9:43 am
Thnk you all for the suggestions.It works now.
June 17, 2012 at 9:16 pm
Can u please telll me which anser is correct....
June 18, 2012 at 7:04 am
sathish463 (6/17/2012)
Can u please telll me which anser is correct....
I highly doubt that any single response was the "answer". There were several different things posted and it was most likely a combination of those things that worked.
If you have an issue with something you should start your own thread and you will get lots of help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply