March 23, 2020 at 4:02 pm
I am getting 2 outputs from this 2 select statement.
Issue is Either one of the select has No records than I am not getting any result. Please let me know is I am doing something wrong.
For example If both the table has records I am getting result perfectly fine, If one table has no records to return than I am getting blank email.
SET NOCOUNT ON
USE TestDC
GO
DECLARE @tableHTML1 NVARCHAR(MAX) ;
SET @tableHTML1 =
N'
Report - New Testing 2
' +
N'<table border="1">' +
N'<tr><th>ID</th><th>Name</th></tr>' +
CAST (( Select td = ID , '',
td = [name]
From dbo.Test
where id =3
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
DECLARE @tableHTML2 NVARCHAR(MAX) ;
SET @tableHTML2 =
N'
Report - New Test
' +
N'<table border="1">' +
N'<tr><th>ID</th><th>Name</th></tr>' +
CAST (( Select td = ID, '',
td = name
From dbo.Test2
where id =3
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
DECLARE @tableHTML NVARCHAR(MAX) ;
Set @tableHTML = @tableHTML1 + @tableHTML2;
EXEC msdb.dbo.sp_send_dbmail
@body_format ='HTML',
@recipients = 'xyz@email.com',
@body = @tableHTML ,
@subject = 'Report - Testing'?
March 23, 2020 at 4:12 pm
Make sure if there are no rows that you have some value (not null) in the variables. You could do something like
SET @tableHTML = COALESCE(@tableHTML1, ' ') + COALESCE(@tableHTML2, ' ')
March 23, 2020 at 4:30 pm
Steve thanks a lot it is working after your suggestion
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply