Is it capable in SQL 2008 to combine the EXEC msdb.dbo.sp_send_dbmail With HTML format and with a CTE table

Transact-SQL
1

hello, I was wondering if it capable in SQL 2008 to combine the EXEC msdb.dbo.sp_send_dbmail With HTML format and with a CTE table.

I have a "real" table ( ExchRate ) where we store 1 exchange Rate EUR- USD per month. These records have as date (ExchRate.Erdate) the last date of Month and of course other columns as fixingprice, buy price etc..

I wrote a query to check if months records missing from table ExchRate in a range of 3 years.

This query seems to be working ( i think that you have better code for this - please tell me your opinions ).
My question is: Is it possible to combine this code with EXEC msdb.dbo.sp_send_dbmail and HTML format?

I want this because I want SQL sending me an email with the results of the query. The best scenario of all will be the SQL send Email ONLY if the query results have Rows.

Here is the CTE table code.
Declare @Start datetime
Declare @End datetime
Select @Start = DATEADD (YEAR,DATEDIFF (YEAR,0, GETDATE()),-1095)
Select @End = GETDATE()

;With CTE as
(
Select @Start as Date,Case When DatePart(mm,@Start)<>DatePart(mm,@Start+1) then 1 else 0 end as [Last]
UNION ALL
Select Date+1,Case When DatePart(mm,Date+1)<>DatePart(mm,Date+2) then 1 else 0 end
from CTE
Where Date<@End
)

Select date as Does_Not_Exist_Record from CTE

LEFT JOIN LIVE.clroot.ExchRate ON (CTE.Date=LIVE.clroot.ExchRate.ErDate)
where ErDate IS NULL AND
[Last]=1 OPTION ( MAXRECURSION 0 )

Here is a very nice code HTML (with CSS) i found after research in Google,with embedded the sp_send_dbmail code, and i want to combine it with CTE query.

This Code works for me for a simpler Query (Select from client....)

DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableHTML nvarchar(max)

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

SET @tableHTML =
N'
#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; }
'+
N'

Customers With Problems

' +
N'' +
N'' + CAST ( (

SELECT
td = [CSACODE],'',
td = ISNULL ([LASTNAME],''),'',
td = ISNULL ([FirstName],'') ,''

FROM Client
WHERE ISNULL ([FirstName],'')= 'George'
ORDER BY [TOTCNWVALP]
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'

CUSTOMER_CODE LASTNAME FIRSTNAME
'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SERVER_ADMIN',
@body = @tableHTML,
@body_format ='HTML',
@recipients = 'User@domaim.GR',
@subject = 'TEST E-mail in Tabular Format' ;

Thanks a lot for your time!!

2

Could this work? I just used my own sample table

declare @query varchar(max) = '
	Declare @Start datetime
	Declare @End datetime
	Select @Start = DATEADD (YEAR,DATEDIFF (YEAR,0, GETDATE()),-1095)
	Select @End = GETDATE()

	;With CTE as
	(
		Select @Start as Date,Case When DatePart(mm,@Start)<>DatePart(mm,@Start+1) then 1 else 0 end as [Last]
		UNION ALL
		Select Date+1,Case When DatePart(mm,Date+1)<>DatePart(mm,Date+2) then 1 else 0 end
		from CTE
		Where Date<@End
	)

	Select date as Does_Not_Exist_Record from CTE

	LEFT JOIN ExchRate ON (CTE.Date=ExchRate.ErDate)
	where ErDate IS NULL AND
	[Last]=1 OPTION ( MAXRECURSION 0 )
'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SERVER_ADMIN',
@body = @tableHTML,
@body_format ='HTML',
@query =@query,  ---adding your data fetcher here?
@recipients = 'User@domaim.GR',
@subject = 'TEST E-mail in Tabular Format' ;
3

What do you mean by "---adding your data fetcher here?"

4

your dynamic @query, was just pointing out where you put in the new parameter. you can disregard the comment