asked on

Building content for email to send email in Tabular Format

Drop table #mytemp
declare @au_id varchar( 100 )
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
set rowcount 0
select distinct Department into #mytemp from [HumanResources].[vEmployeeDepartment] where Department in ('Sales','Production')
set rowcount 1
select @au_id = Department from #mytemp
while @@rowcount <> 0
begin
set rowcount 0
Drop table #mytempTemp
      select Top 5 * into #mytempTemp from [HumanResources].[vEmployeeDepartment] where Department = @au_id
      SET @xml = CAST(( SELECT [FirstName] AS 'td','',[LastName] AS 'td','',
      [Department] AS 'td'
      FROM #mytempTemp ORDER BY LastName, FirstName
      FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
      SET @body ='<html><body><H3>Details as follows:</H3>
      <table border = 1>
      <tr>
      <th> First Name </th> <th> Last Name </th> <th> Department </th></tr>'
     SET @body = @body + @xml +'</table></body></html>'
      EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'Notifications', -- replace with your SQL Database Mail Profile
      @body = @body,
      @body_format ='HTML',
      @recipients = '@Emailaddress', -- replace with your email address
      @subject = 'E-mail in Tabular Format - Testing' ;

delete #mytemp where Department = @au_id
delete #mytempTemp where Department = @au_id
set rowcount 1
select @au_id = Department from #mytemp
end
set rowcount 0

ASKER CERTIFIED SOLUTION

WestminsterWebDev

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial