Need Help with sending email only when result fetches atleast one record

Question

Post reply

Need Help with sending email only when result fetches atleast one record

wweraw25

SSC Eights!

Points: 953
More actions
December 19, 2016 at 9:41 am

#401234

Hello Folks,
Lets say if I'm using the below to send email
DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableHTML nvarchar(max)
SET @subject = 'Query Results in HTML with CSS'
SET @tableHTML =
N'<style type="text/css">
#box-table
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 12px;
text-align: center;
border-collapse: collapse;
border-top: 7px solid #9baff1;
border-bottom: 7px solid #9baff1;
}
#box-table th
{
font-size: 13px;
font-weight: normal;
background: #b9c9fe;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #039;
}
#box-table td
{
border-right: 1px solid #aabcfe;
border-left: 1px solid #aabcfe;
border-bottom: 1px solid #aabcfe;
color: #669;
}
tr:nth-child(odd){ background-color:#eee; }
tr:nth-child(even){ background-color:#fff; }
</style>'+
N'<H3><font color="Red">All Rows From [AdventureWorks].[Sales].[SpecialOffer]</H3>' +
N'<table id="box-table" >' +
N'<tr><font color="Green"><th>SpecialOfferID</th>
<th>Description</th>
<th>Type</th>
<th>Category</th>
<th>StartDate</th>
<th>EndDate</th>
</tr>' +
CAST ( (
SELECT td = CAST([SpecialOfferID] AS VARCHAR(100)),'',
td = [Description],'',
td = [Type],'',
td = [Category] ,'',
td = CONVERT(VARCHAR(30),[StartDate],120) ,'',
td = CONVERT(VARCHAR(30),[EndDate],120)
FROM [AdventureWorks].[Sales].[SpecialOffer]
ORDER BY [SpecialOfferID]
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'
EXEC msdb.dbo.sp_send_dbmail @recipients='AnyMailYouWant@SqlIsCool.com',
@subject = @subject,
@body = @tableHTML,
@body_format = 'HTML' ;
I want this to be modified to
Send email only when the @tableHTML generates atleast one record otherwise no email should be sent.

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

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

Lowell SSC Guru Points: 323507 More actions · Answer 1

i always insert into a temp table,and then check if exists:

SELECT *

INTO #temp

FROM [AdventureWorks].[Sales].[SpecialOffer]WHERE SomeFilterOrColumn =SomeValue

IF EXISTS(SELECT * FROM temp)

BEGIN

--all the email logic you had previously

--but FORXML select from temp table instead of the original table

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

wweraw25 SSC Eights! Points: 953 More actions · Answer 2

Can you be more specific on the FOR XML thing

Thanks in Advance

Lowell SSC Guru Points: 323507 More actions · Answer 3

here's an example of your code.

your example has no where statement. which doesn't make sense to me, you'd want to filter on a table,a nd send if there is data.

i highlighted the FROM and FOR XML portion of your code, but you have to scroll a bit to see it in the code window.

SELECT *

INTO #temp

FROM [AdventureWorks].[Sales].[SpecialOffer] --WHERE SomeFilterOrColumn =SomeValue

IF EXISTS(SELECT * FROM #temp)

BEGIN

DECLARE @bodyMsg nvarchar(max)

DECLARE @subject nvarchar(max)

DECLARE @tableHTML nvarchar(max)

SET @subject = 'Query Results in HTML with CSS'

SET @tableHTML =

N'<style type="text/css">

#box-table

{

font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;

font-size: 12px;

text-align: center;

border-collapse: collapse;

border-top: 7px solid #9baff1;

border-bottom: 7px solid #9baff1;

}

#box-table th

{

font-size: 13px;

font-weight: normal;

background: #b9c9fe;

border-right: 2px solid #9baff1;

border-left: 2px solid #9baff1;

border-bottom: 2px solid #9baff1;

color: #039;

}

#box-table td

{

border-right: 1px solid #aabcfe;

border-left: 1px solid #aabcfe;

border-bottom: 1px solid #aabcfe;

color: #669;

}

tr:nth-child(odd){ background-color:#eee; }

tr:nth-child(even){ background-color:#fff; }

</style>'+

N'<H3><font color="Red">All Rows From [AdventureWorks].[Sales].[SpecialOffer]</H3>' +

N'<table id="box-table" >' +

N'<tr><font color="Green"><th>SpecialOfferID</th>

<th>Description</th>

<th>Type</th>

<th>Category</th>

<th>StartDate</th>

<th>EndDate</th>

</tr>' +

CAST ( (

SELECT td = CAST([SpecialOfferID] AS VARCHAR(100)),'',

td = [Description],'',

td = [Type],'',

td = [Category] ,'',

td = CONVERT(VARCHAR(30),[StartDate],120) ,'',

td = CONVERT(VARCHAR(30),[EndDate],120)

[highlight="#ffff11"]FROM #temp

ORDER BY [SpecialOfferID]

FOR XML PATH('tr'), TYPE [/highlight]

) AS NVARCHAR(MAX) ) +

N'</table>'

EXEC msdb.dbo.sp_send_dbmail @recipients='AnyMailYouWant@SqlIsCool.com',

@subject = @subject,

@body = @tableHTML,

@body_format = 'HTML' ;

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

wweraw25 SSC Eights! Points: 953 More actions · Answer 4

Thank you very much for your input. It worked for now..