Send mail when the query has resultset

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Send mail when the query has resultset

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2011-08-24 : 03:38:24

DECLARE @Color VARCHAR(50)
declare @UserTable TABLE(UserID VARCHAR(50), EmailID VARCHAR(50), ReportingL1 VARCHAR(50))
INSERT INTO @UserTable
SELECT '8829', 'rkbabu@gmail.in', '5841' UNION ALL
SELECT '5841', 'nsuresh@gmail.in', '11519' UNION ALL
SELECT '10816', 'rkalaivani@gmail.in', '5841' UNION ALL
SELECT '5841', 'nsuresh@gmail.in', '11519'

declare @QcardTable TABLE(UserID VARCHAR(50), AlertType INT)
INSERT INTO @QcardTable
SELECT '8829', 1 UNION ALL
SELECT '10816', 2

--If the below query has result set we have to send the mail
SELECT QT.UserID, UT.EmailID AS ToEmailID, RT.EmailID AS CCEmailID, QT.AlertType FROM @QcardTable AS QT
INNER JOIN @UserTable AS UT ON
UT.UserID = QT.UserID
INNER JOIN @UserTable AS RT ON
RT.UserID = UT.ReportingL1
GROUP BY QT.UserID, UT.EmailID, RT.EmailID, QT.AlertType

/*
rkalaivani@gmail.in nsuresh@gmail.in 2
rkbabu@gmail.in nsuresh@gmail.in 1
*/

SET @Color = 'GREEN'

DECLARE @MailSubject VARCHAR(8000)
DECLARE @MailHeader VARCHAR(500)
DECLARE @MailBody VARCHAR(8000)
set @MailSubject = 'YELLOW alert'
set @MailHeader = @MailSubject
set @MailBody = '<html xmlns="http://www.w3.org/1999/xhtml" >
<body>
<table border="2" cellspacing="0" cellpadding="0" bordercolor="FFFF00" style="font:verdana normal 12; color:'+@Color+';">
<tr>
<th colspan="2">'+@MailHeader+'</th>
</tr>
</table>
</body>
</html>'

EXEC [MSDB].dbo.sp_send_dbmail @profile_name = 'DBA',
@recipients =
,@copy_recipients =
--,@query = @str
,@subject= @MailSubject
,@body = @MailBody
,@body_format = 'HTML'
--,@query_attachment_filename ='details.xls'
--,@attach_query_result_as_file = 1
,@query_result_width = 300

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 04:19:48

get @@ROWCOUNT value in a variable after query check if its >0 and inside if condition call your sp_send_Dbmail procedure to send the mail

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-08-24 : 04:44:06

I think you are going to have to loop (or CURSOR) around


SELECT ... FROM @QcardTable ...

too, putting ToEmailID, CCEmailID, etc. into @Variables to then pass them to sp_send_dbmail

Personally I would add IDENTITY(1,1) column to @QcardTable and use a loop to process each row.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources