CODEWITHSUNDEEP
sqlsql-server
PJLG
PJLG

Reputation: 105

SQL Query to send e-mail based on records number

I have this sql query:

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
N'<H1>Table name</H1>' +
N'<table border="1">' +
N'<tr><th>Column name1</th><th>Column name2</th>' +
N'<th>Column name3</th><th>Column name4</th></tr>' +
CAST ( ( select td = login_time, '', 
                td = last_batch, '', 
                td = hostname ,'' ,
                td = loginame, ''
    from master.dbo.sysprocesses
    where loginame<>'sa' and program_name=''
    order by last_batch Desc
          FOR XML PATH('tr'), TYPE 
) AS NVARCHAR(MAX) ) +
N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'profile name',
@recipients='e-mail account',
@subject = 'e-mail subject',
@body = @tableHTML,
@body_format = 'HTML' ;

But I need only send the e-mail if there are records on query.

Can someone help???

Thank you.

Upvotes: 0

Views: 2582

Answers (1)

George Mastros
George Mastros

Reputation: 24498

Just check for the existence of data first. Something like this:

DECLARE @tableHTML  NVARCHAR(MAX) ;

If Exists(Select 1 from master.dbo.sysprocesses
    where loginame<>'sa' and program_name='')
    Begin

        SET @tableHTML =
        N'<H1>Table name</H1>' +
        N'<table border="1">' +
        N'<tr><th>Column name1</th><th>Column name2</th>' +
        N'<th>Column name3</th><th>Column name4</th></tr>' +
        CAST ( ( select td = login_time, '', 
                        td = last_batch, '', 
                        td = hostname ,'' ,
                        td = loginame, ''
            from master.dbo.sysprocesses
            where loginame<>'sa' and program_name=''
            order by last_batch Desc
                  FOR XML PATH('tr'), TYPE 
        ) AS NVARCHAR(MAX) ) +
        N'</table>' ;

        EXEC msdb.dbo.sp_send_dbmail 
        @profile_name = 'profile name',
        @recipients='e-mail account',
        @subject = 'e-mail subject',
        @body = @tableHTML,
        @body_format = 'HTML' ;
    End

Upvotes: 1

Related Questions