October 27, 2010 at 11:36 am
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
October 27, 2010 at 1:29 pm
See "formatting stuff" here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254
N 56°04'39.16"
E 12°55'05.25"
October 27, 2010 at 2:15 pm
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.
October 27, 2010 at 2:28 pm
It uses the "quirky update". Jeff Moden wrote an article about the quirky update.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 27, 2010 at 3:38 pm
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.
October 27, 2010 at 8:37 pm
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]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply