1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
| CREATE PROCEDURE maProcedure
AS
BEGIN
SET NOCOUNT ON;
DECLARE @html_body varchar(max)
, @html_table_header varchar(max)
, @html_table_footer varchar(max)
, @mail_subject varchar(128) = 'Désignation et quantité';
SELECT @html_table_footer = '</table></body></html>'
, @html_table_header = '<html><head>' +
'<style>
td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;}
tr.even {background-color:white;}
tr.odd {background-color:#eeeeee;}
</style>' +
'</head>' +
'<body><table cellpadding=0 cellspacing=0 border=0>' +
'<tr bgcolor=#FFEFD8><td align=center><b>Designation</b></td>' +
'<td align=center><b>qteDemandee</b></td></tr>';
SELECT @html_body =
(
SELECT DDA.designation AS td
, DDA.qteDemandee AS td
FROM DetailDemandeAchat AS DDA
INNER JOIN INSERTED AS I
ON DDA.numDemande = I.numDemande
FOR XML RAW('tr'), ELEMENTS
);
IF @html_body IS NOT NULL AND LEN(@html_body) > 0
BEGIN
SET @html_body = REPLACE(@html_body, '_x003D_', '=');
SET @html_body = REPLACE(@html_body, '_x0020_', ' ');
SET @html_body = REPLACE(@html_body, '<tr><TRRow>0</TRRow>', '<tr class="even">');
SET @html_body = REPLACE(@html_body, '<tr><TRRow>1</TRRow>', '<tr class="odd">');
SET @html_body = @html_table_header + @html_body + @html_table_footer;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'PROFILMAIL SQL'
, @recipients = 'mail'
, @body = @html_body
, @subject = @mail_subject
, @body_format = 'HTML'
, @importance ='HIGH';
END
END |
Partager