Merhabalar ,
İş Süreçlerimizin yönetilmesi açısından gerekli bir araç olan MSSQL html mail gönderme işlemine , Kullanıcı bazlı ve Adet mail gönderme işlemlerimizi yapabiliriz.
Örnek Kodlama ;
ALTER PROCEDURE SP_DIGITAL_FATURAGONDERIM
AS
BEGIN
DECLARE @EMAIL NVARCHAR(150)
DECLARE @ONAY_NUM NVARCHAR(150)
DECLARE @USER_NAME NVARCHAR(150)
DECLARE @SAY TINYINT
DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
SELECT USR.USER_NAME,COUNT(FAT.ONAYNUM) AS SAY,USR.EMAIL,FAT.ONAYNUM
FROM TBL_FATURALAR AS FAT INNER JOIN TBL_USER AS USR ON USR.ID = FAT.ONAYNUM
GROUP BY FAT.ONAYNUM,USR.EMAIL,USR.USER_NAME
HAVING COUNT(FAT.ONAYNUM) > 5
OPEN cur
FETCH NEXT FROM cur INTO @USER_NAME,@SAY, @EMAIL,@ONAY_NUM
WHILE @@FETCH_STATUS = 0 BEGIN
DECLARE @Body NVARCHAR(MAX),
@TableHead VARCHAR(1000),
@TableTail VARCHAR(1000)
SET @TableTail = ‘</table></body></html>’ ;
SET @TableHead = ‘<html><head>’ + ‘<style>’
+ ‘td {border: solid black;border-width: 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font: 11px arial} ‘
+ ‘</style>’ + ‘</head>’ + ‘<body>’ + ‘Sayın : ‘ + UPPER(@USER_NAME) + ‘ ‘+CONVERT(NVARCHAR(5), @SAY)+ ‘ Adet kontrol etmeniz gereken faturanız bulunmaktadır. Raporunuzun oluşturma Tarihi : ‘
+ CONVERT(VARCHAR(50), GETDATE(), 104)
+ ‘ <br>
<br>
<table cellpadding=0 cellspacing=0 border=0>’
+ ‘<tr> <td bgcolor=#E6E6FA><b>Fiş Numarası</b></td>’
+ ‘<td bgcolor=#E6E6FA><b>Cari İsim</b></td>’
+ ‘<td bgcolor=#E6E6FA><b>Stok Adı</b></td>’
+ ‘<td bgcolor=#E6E6FA><b>Miktar</b></td>’
+ ‘<td bgcolor=#E6E6FA><b>Toplam Tutar</b></td>
</tr>’ ;
SET @Body = ( SELECT FISNO AS ‘td’,CARI_ISIM AS ‘td’,STOK_ADI AS ‘td’,MIKTAR AS ‘td’,CONVERT(DECIMAL(8,2),TOPLAM_TUTAR) AS ‘td’
FROM [DYR_KAP_DB].[dbo].[TBL_FATURALAR] WHERE ONAYNUM = @ONAY_NUM
FOR XML RAW(‘tr’),
ELEMENTS
)
SELECT @Body = @TableHead + ISNULL(@Body, ”) + @TableTail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘ sqlmailprofile’,
@body = @Body,
@body_format =’HTML’,
@recipients = @EMAIL,
@subject = ‘Fatura Kontrol bilgilendirme’ ;
FETCH NEXT FROM cur INTO @USER_NAME,@SAY, @EMAIL,@ONAY_NUM
END
CLOSE cur
DEALLOCATE cur
END
Faydalı olması dileğiyle
Ömer ÇARNAÇAR
MCSA - MCT
www.omercarnacar.com
