 |
 |
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.
|
|
|
|
If you are looking for a prestigious brand name watches that are a mix of quality craftsmanship and contemporary styling, one need look no further than MontBlanc fake????? Keep your time in style with beautifully designed Dewitt fake Lange+1
That is why Cartier fake Breguet watches will be a lot cheaper than the real ones
When it comes to gifting your loved one a photocopy of a classic watch minus its wallet-wrenching price, RogerDubuis replica from the world’s classics come in handy
Our high quality services ensure that every shopper can find the replica Rolex they want at a price they can afford
In such a case, replica Guess watches of this indigenous brand can save the day
Our company has friendly and knowledgeable representatives available to answer any questions you have about our replica Baume Mercier watches any time that is convenient for you
Only your jeweler with his loop in his eye can tell, you are the owner of a one of the Longines fake
|
|
Note: Only registered ShinyDonkey.com users
can post images. Only administrators can delete images.
|
|
|