SQL Server Stored Procedure Tutorial

2 Comments

1. excellent article.

2. 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?