 |
 |
COALESCE, Man!
[reply]
|
10/14/05 06:31 AM EST
posted by JER email web |
|
I was writing some SQL for my football pool site the other day and wanted to retrieve a list of "Recent Mass Mailings" and who they were sent to. I have an EmailLog table that is keyed on DateTimeSent & ToAddress. Some might consider this a weak key since two separate mass e-mails could theoretically be generated at the same time, but let's ignore that for now. So if a mass mail is sent to 50 people, there would be 50 records in the log table. To display the last 5 mailings, though, I group by Subject & DateTimeSent. I want a list of "ToAddress" values to show up in my output, though, so that my final result would look something like this:
| Message | Time Sent | Sent to | | Washington Job Listings | 10/12/2005 3:00 pm | elliotF@myplace.com
johnF@tcby.com | | Hot New Clubs | 10/11/2005 2:10 pm | bull@krosschell.com
natesmith@biggay.com |
This could be done fairly easily in the code behind or using a fancy Repeater control, but I wanted to do it all in one SQL query. After all these years, I'm still obsessed with writing the ONE QUERY solution to solve everything.
Anyway, I did it by adding a UDF to get a list of addresses that have the same timestamp. I used a cursor to loop over records and appended them to a big string. Note that I replace the commas with BR tags in my aspx page. As much as I advocate the ONE QUERY solution, I cringe when I see HTML in SQL code.
CREATE FUNCTION dbo.udf_GetEmailLogRecipients (@DateTimeStamp datetime)
RETURNS varchar(8000) AS
BEGIN
DECLARE @results varchar(8000), @emailAddress varchar(200)
SET @results = ''
DECLARE log_cursor CURSOR FOR
SELECT ToAddress
FROM EmailLog
WHERE DateTimeSent = @DateTimeStamp
ORDER BY ToAddress ASC
OPEN log_cursor
FETCH NEXT FROM log_cursor INTO @emailAddress
WHILE @@FETCH_STATUS = 0
BEGIN
IF @results = ''
SET @results = @emailAddress
ELSE
SET @results = @results + ',' + @emailAddress
FETCH NEXT FROM log_cursor INTO @emailAddress
END
CLOSE log_cursor
DEALLOCATE log_cursor
RETURN @results
END
So I wrote that code yesterday & it worked out well. The cursor approach seemed extravagant, but it worked and I was satisfied that I'd gotten everything into 1 query. Lo and behold, 4 Guys from Rolla had an article today discussing this exact same issue and pointed out that the COALESCE statement is meant to be used for exactly this purpose!
CREATE FUNCTION dbo.udf_GetEmailLogRecipients (@DateTimeStamp datetime)
RETURNS varchar(8000) AS
BEGIN
DECLARE @results varchar(8000)
SELECT @results = COALESCE(@results + ', ', '') + ToAddress
FROM EmailLog
WHERE DateTimeSent = @DateTimeStamp
RETURN @results
END
This new UDF is much cleaner & I'm confident that just about ANY command is faster than using a cursor, so I'd guess performance will be better, too. |
|
|
|
Woah. The Evil Spammers do harvest email addresses from webpages, you know.
Using real email addresses, un-obfuscated, isn't so nice.
I'm sure the fine folks over at biggay.com don't want their Nate Smith receiving even more spam. |
|
|
|
| Such attention to detail... I knew if anyone would catch those jokes, it would be you. Or maybe Alex, because he owns most of those domain names. |
|
[reply]
|
10/14/05 10:43 AM EST
posted by Doug1 web |
|
Dude, you consider using cursors one query? With that kind of
performance hit, you might as well use two. Seems simpler to just get
it all and toggle your CFOUTPUT GROUP= :-)
But seriously, I am the same way. I don't know how many times I've
heard mockingly "I bet Doug could do it in one query" and then I do.
Still haven't had a need for cursors yet.
|
|
Note: Only registered ShinyDonkey.com users
can post images. Only administrators can delete images.
|
|
|