October 26, 2011 at 7:37 am
You might as well insert the 10 parameters in the tvp (without bothering with ifs) and then use where value is not null in the concat function.
October 26, 2011 at 8:06 am
Ninja's_RGR'us (10/26/2011)
You might as well insert the 10 parameters in the tvp (without bothering with ifs) and then use where value is not null in the concat function.
If you use the For XML query, you don't need to worry about Is Not Null. For XML drops null-value columns unless you specify XSINIL in the clause. You could include Where Not Null if you want to do the Explicit-vs-Implicit thing, for pseudodocumentation, but it's not needed.
To see this:
--Using the TVP and UDF I created in my prior sample:
DECLARE @Vals AS TVPtest;
INSERT INTO @Vals(Val)
VALUES ('Val1'),('Val2 & Val3'),('Val4'),(NULL),('Val6');
SELECT dbo.ConcatString (@Vals);
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2011 at 8:11 am
Thanks for the info.
As I said earlier, not really expert in xml :-).
October 26, 2011 at 10:10 am
Thanks for the responses. I've used the version started by Gianluca Sartori and slightly adjusted by GSquared to give me perfect results. I'll live with passing null values into the function to fill the slots.
Ta muchly everyone!
Stuart
October 26, 2011 at 11:18 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 27, 2011 at 10:30 am
phual (10/26/2011)
I have a need to concatonate the values of ten fields together, but to only include a separator if there are values to separate.E.g.
@Param1 = NULL
@Param2 = 'Value1'
@Param3 = NULL
@Param4 = NULL
@Param5 = 'Value2'
@Param6 = NULL
@Param7 = NULL
@Param8 = NULL
@Param9 = NULL
@Param10 = NULL
Return 'Value1; Value2'
The problem I have is than any solution I come up with is huge and complicated. I can't help but feel that I'm using a sledgehammer to crack a nut.
What would others suggest as an approach to this?
Stuart
select
replace(
isnull(@Param1+';','')
+isnull(@Param2+';','')
+isnull(@Param3+';','')
+isnull(@Param4+';','')
+isnull(@Param5+';','')
+isnull(@Param6+';','')
+isnull(@Param7+';','')
+isnull(@Param8+';','')
+isnull(@Param9+';','')
+isnull(@Param10+';','')
+'|',
';|','');
Value1;Value2
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply