I have write a query that send me a message from my DB, containing some information of my tables. this is the code:
USE [Database-name];
GO
/*Declare Variables for HTML*/
DECLARE @Style NVARCHAR(MAX)= '';
DECLARE @tableHTML NVARCHAR(MAX)= '';
/*Define CSS for html to use*/
SET @Style += +N'<style type="text/css">' + N'.tg {border-collapse:collapse;border-spacing:0;border-color:#aaa;}'
+ N'.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aaa;color:#333;background-color:#fff;}'
+ N'.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aaa;color:#fff;background-color:#0F3870;}'
+ N'.tg .tg-9ajh{font-weight:bold;background-color:lightgrey}' + N'.tg .tg-hgcj{font-weight:bold;text-align:center}'
+ N'</style>';
SET @tableHTML = @Style + @tableHTML + N'<H2>CDR Summary</H2>'
+ N'<table class="tg">' --DEFINE TABLE
/*Define Column Headers and Column Span for each Header Column*/
+ N'<tr>'
+ N'<th class="tg-hgcj" colspan="4">CDR</th>'
+ N'</tr>'
+ N'</tr>'
/*Define Column Sub-Headers*/
+ N'<tr>'
+ N'<td class="tg-9ajh">Total Attempt</td>'
+ N'<td class="tg-9ajh">Total Duration in Min</td>'
/*Define data for table and cast to xml*/
+ CAST(( SELECT td = CONVERT(CHAR(8), count(id), 112),
'',
td = CAST(SUM(DurationInSeconds) / 60 AS NUMERIC(18,2)) ,
''
FROM [TOneWhS_CDR].[CDR]
FOR
XML PATH('tr') ,
TYPE
) AS NVARCHAR(MAX))
+ N'</table>'
+ N'</br>'
+ N'</br>'
+ N'<table class="tg">' --DEFINE TABLE
/*Define Column Headers and Column Span for each Header Column*/
+ N'<tr>'
+ N'<th class="tg-hgcj" colspan="2">CDR Main</th>'
+ N'<th class="tg-hgcj" colspan="2">CDR Invalid</th>'
+ N'<th class="tg-hgcj" colspan="2">CDR Failed</th>'
+ N'<th class="tg-hgcj" colspan="2">CDR Partial</th>'
+ N'<th class="tg-hgcj" colspan="2">CDR interconnect</th>'
+ N'</tr>'
/*Define Column Sub-Headers*/
+ N'<tr>'
+ N'<td class="tg-9ajh">Total Attempt</td>'
+ N'<td class="tg-9ajh">Total Duration in Min</td>'
+ N'<td class="tg-9ajh">Total Attempt</td>'
+ N'<td class="tg-9ajh">Total Duration in Min</td>'
+ N'<td class="tg-9ajh">Total Attempt</td>'
+ N'<td class="tg-9ajh">Total Duration in Min</td>'
+ N'<td class="tg-9ajh">Total Attempt</td>'
+ N'<td class="tg-9ajh">Total Duration in Min</td>'
+ N'<td class="tg-9ajh">Total Attempt</td>'
+ N'<td class="tg-9ajh">Total Duration in Min</td>'
/*Define data for table and cast to xml*/
+ CAST(( SELECT td = CONVERT(CHAR(8), count(a.CDRId), 112),
'',
td = CAST(SUM(a.DurationInSeconds) / 60 AS NUMERIC(18,2)),
'',
td = CONVERT(CHAR(8), count(b.CDRId), 112),
'',
td = CAST(SUM(b.DurationInSeconds) / 60 AS NUMERIC(18,2)),
'',
td = CONVERT(CHAR(8), count(c.CDRId), 112),
'',
td = CAST(SUM(c.DurationInSeconds) / 60 AS NUMERIC(18,2)),
'',
td = CONVERT(CHAR(8), count(d.CDRId), 112),
'',
td = CAST(SUM(d.DurationInSeconds) / 60 AS NUMERIC(18,2)),
'',
td = CONVERT(CHAR(8), count(e.CDRId), 112),
'',
td = CAST(SUM(e.DurationInSeconds) / 60 AS NUMERIC(18,2)),
''
from [table1] a
full join [table2] b
on a.CDRId = b.CDRId
full join [table3] c
on a.CDRId = c.CDRId
full join [table4] d
on a.CDRId = d.CDRId
full join [table] e
on a.CDRId = e.CDRId
FOR
XML PATH('tr') ,
TYPE
) AS NVARCHAR(MAX))
+ N'</table>'
+ N'</br>'
+ N'</br>'
+ N'<table class="tg">' --DEFINE TABLE
/*Define Column Sub-Headers 2 RESULT SECTION!!!!!!*/
+ N'<tr>'
+ N'<td class="tg-9ajh">Result</td>'
+ N'<td class="tg-9ajh" style="background-color: red;"></td>'
/*Define data for table and cast to xml*/
+ 'MISSING CODE'
+ N'</table>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mahmoud El Khatib',
@recipients = 'my email',
@body = @tableHTML,
@body_format = 'HTML',
@subject = 'CDR';
--------------------------------------------------------------------------------------------
This code generates a table and information sent to my email with specific design in CSS section.[this is the output of the code above][1]
--------------------------------------------------------------------------------------------
In the result section, I want to put a procedure that check my database in a true or false statement, if it's true it will write "All good!" with background green, if it's false it will write "All Bad!" with background red.
this is my procedure
DROP procedure IF EXISTS spchecktables
GO
create procedure spchecktables
as
declare @cdr int
declare @maincdrtable int
declare @invalidcdrtable int
declare @failedcdrtable int
declare @interconnectcdrtable int
declare @partialcdrtable int
select @cdr = count(id) from [TOneWhS_CDR].[CDR]
select @maincdrtable = count(CDRId) from [TOneWhS_CDR].[BillingCDR_Main]
select @invalidcdrtable = count(CDRId) from [TOneWhS_CDR].[BillingCDR_Invalid]
select @failedcdrtable = count(CDRId) from [TOneWhS_CDR].[BillingCDR_Failed]
select @interconnectcdrtable = count(CDRId) from [TOneWhS_CDR].[BillingCDR_Interconnect]
select @partialcdrtable = count(CDRId) from [TOneWhS_CDR].[BillingCDR_PartialPriced]
if (@cdr = @maincdrtable + @invalidcdrtable + @failedcdrtable + @interconnectcdrtable + @partialcdrtable )
print 'All is good!'
else
print 'all is bad'
go
exec spchecktables;
--------------------------------------------------------------------------------------------
MY REQUEST IS: how to embed my procedure in my first code?
|
