How does my concatenation UDF work?

Question

Post reply

How does my concatenation UDF work?

traughber

SSC-Addicted

Points: 419
More actions
October 27, 2010 at 11:36 am

#228442

I created a UDF that builds a proper letter salutation string (e.g., Dear Kris, John, and Bill) depending on how many names are in the result set. I got part of the SQL from a website somewhere and I added to it to make it work for my purposes. It works beautifully. However, the part I got online is what is doing the actual concatenation of the string but I don't quite understand how it works. Let me show you.
Here's the whole UDF.
ALTER FUNCTION dbo.fnConcatenate
(
@ClientID varchar(10),
@ParticipantID int,
@CompanyID int
)
RETURNS VARCHAR(100) AS
BEGIN
DECLARE @Return VARCHAR(100)
DECLARE @Count INT
SELECT @Count = COUNT(DISTINCT c.fname)
FROM vProjects p
INNER JOIN p_contacts pc ON p.id = pc.proj_id
INNER JOIN contacts c ON pc.contact_id = c.id
LEFT JOIN c_firm cf ON c.id_firm = cf.id
INNER JOIN ParticipantContactXref pcx ON c.id = pcx.contact_id
INNER JOIN participants pt ON pcx.participant_id = pt.id
AND pt.proj_id = p.id
WHERE client_id = @ClientID
AND participant_id = @ParticipantID
AND id_firm = @CompanyID
SELECT @Return =
CASE
WHEN @Count = 1 THEN LTRIM(RTRIM(c.fname))
WHEN @Count = 2 THEN ISNULL(@Return+' and ','')+LTRIM(RTRIM(c.fname))
WHEN @Count >= 3 THEN ISNULL(@Return+', ','')+LTRIM(RTRIM(c.fname))
END
FROM vProjects p
INNER JOIN p_contacts pc ON p.id = pc.proj_id
INNER JOIN contacts c ON pc.contact_id = c.id
LEFT JOIN c_firm cf ON c.id_firm = cf.id
INNER JOIN ParticipantContactXref pcx ON c.id = pcx.contact_id
INNER JOIN participants pt ON pcx.participant_id = pt.id
AND pt.proj_id = p.id
WHERE client_id = @ClientID
AND participant_id = @ParticipantID
AND id_firm = @CompanyID
ORDER BY c.fname
RETURN ISNULL(LEFT(@Return,LEN(@Return)-CHARINDEX(',',REVERSE(@Return))) + REPLACE(RIGHT(@Return,CHARINDEX(',',REVERSE(@Return))),', ',', and '),'NOTHING')
END
The part I copied from online that performs the concatenation is:
ISNULL(@Return+', ','')+LTRIM(RTRIM(c.fname))
And I'm using it in this part:
SELECT @Return =
CASE
WHEN @Count = 1 THEN LTRIM(RTRIM(c.fname))
WHEN @Count = 2 THEN ISNULL(@Return+' and ','')+LTRIM(RTRIM(c.fname))
WHEN @Count >= 3 THEN ISNULL(@Return+', ','')+LTRIM(RTRIM(c.fname))
END
FROM vProjects p
INNER JOIN p_contacts pc ON p.id = pc.proj_id
INNER JOIN contacts c ON pc.contact_id = c.id
LEFT JOIN c_firm cf ON c.id_firm = cf.id
INNER JOIN ParticipantContactXref pcx ON c.id = pcx.contact_id
INNER JOIN participants pt ON pcx.participant_id = pt.id
AND pt.proj_id = p.id
WHERE client_id = @ClientID
AND participant_id = @ParticipantID
AND id_firm = @CompanyID
ORDER BY c.fname
My question is that I'm not sure how ISNULL(@Return+', ','')+LTRIM(RTRIM(c.fname)) works. I get what the ISNULL is doing, but the +c.fname is baffling me. If my result set returns 3 rows with 3 different names and all of them are being concatenated together, +c.fname implies that the function is looping through all 3 rows in order to concatenate, but there is no looping structure here at all.
Can someone please explain this. My SQL skills aren't bad (i.e., I wrote the rest of this function without reference materials), but this is escaping me.
Thanks.
Kris

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

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

See "formatting stuff" here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

N 56°04'39.16"
E 12°55'05.25"

traughber SSC-Addicted Points: 419 More actions · Answer 2

Thanks for the reply. I find your example very interesting as I have never seen some of that functionality. I will definitely study it to see what I can learn. However, what I'm asking in my original post is specifically how the @Return+c.fname appears to be acting like a looping structure when there is no looping structure setup.

drew.allen SSC Guru Points: 76985 More actions · Answer 3

It uses the "quirky update". Jeff Moden wrote an article about the quirky update.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

traughber SSC-Addicted Points: 419 More actions · Answer 4

Thanks Drew, but it appears that the link you gave just points right back to this thread. I searched SSC for "quirky update" but didn't find any articles that looked like it. I would love to read the article you mentioned.

Thanks again.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

traughberk (10/27/2010)
Thanks Drew, but it appears that the link you gave just points right back to this thread. I searched SSC for "quirky update" but didn't find any articles that looked like it. I would love to read the article you mentioned.
Thanks again.

This was the link Drew was talking about : Quirky Update To Solve Running Totals By Jeff Moden[/url]