Note: Need to enable email and setup mail profile first.

Example 1

DECLARE @tableHTML NVARCHAR(MAX) ; 

SET @subjectLine = 'Notification: Latest data as of ' + cast(GETDATE() as char)

SET @tableHTML = 
	N'<html><body><h1>Latest Orders</h1>' + 
	N'<table border="1" width="100%">' + 
	N'<tr><td>OrderId</td><td>CustomerId</td><td>Order Total</td><td>Date</td><td>User</td></tr>' + 
			CAST(( 
				SELECT 
					TOP 10 
					  td = ord.OrderId, '',
					  td = ord.CustomerId, '',
					  td = ord.OrderTotal, '',
					  td = ord.Date, '',
					  td = ord.User, ''				
				FROM 
					ls01.db01.dbo.tbl01 ord 
				ORDER BY 
					OrderId DESC
			FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) + N'</table></body></html>' 

	EXEC msdb.dbo.sp_send_dbmail 
	@recipients= 'me@test.com',
	@profile_name ='Default Mail Profile',
	@subject = @subjectLine,
	@body=@tableHTML,
	@body_format = 'HTML'

Example 2

uspConvertTableToHtml Stored Procedure



CREATE PROCEDURE [dbo].[uspConvertTableToHtml](
      @SqlQuery AS NVARCHAR(4000),
      @Html AS VARCHAR(MAX) OUTPUT
)
AS

      DECLARE @Header AS NVARCHAR(MAX) = ''
      DECLARE @Column AS NVARCHAR(MAX) = ''
      DECLARE @Query AS NVARCHAR(MAX)
      DECLARE @Css AS VARCHAR(MAX) = '
            <style type="text/css">

            table.gridtable {
                font-family: verdana,arial,sans-serif;
                font-size:11px;
                color:#333333;
                border-width: 1px;
                border-color: #666666;
                border-collapse: collapse;
            }

            table.gridtable th {
                border-width: 1px;
                padding: 5px;
                border-style: solid;
                border-color: #666666;
                background-color: #3BB9FF;
            }

            table.gridtable td {
                border-width: 1px;
                padding: 5px;
                border-style: solid;
                border-color: #666666;
                background-color: #ffffff;
            }

            </style>
            '
BEGIN

      SET @Query = 'SELECT * INTO ##columns FROM ( ' + @SqlQuery + ') Temp'
      EXECUTE(@Query)

      SELECT @Column = @Column + 'ISNULL(' + QUOTENAME(name) +' ,'' '')' + ' AS TD, '
      FROM tempdb.SYs.columns
      WHERE object_id = OBJECT_ID('tempdb..##columns')
      
      SET  @Column = LEFT(@Column,LEN(@Column)-1)

      SELECT @Header = @Header + '<TH><b>' +  name + '<b/></TH>'
      FROM tempdb.SYs.columns
      WHERE object_id = OBJECT_ID('tempdb..##columns')
      
      SET @Header = '<TR>' + @Header  + '</TR>'
      
      SET @Query = 'SET  @Html = (SELECT ' + @Column + ' FROM ( ' + @SqlQuery + ') AS TR
       FOR XML AUTO ,ROOT(''TABLE''), ELEMENTS)'

      EXECUTE SP_EXECUTESQL @Query,N'@Html VARCHAR(MAX) OUTPUT',@Html OUTPUT
      SET  @Html = @Css + REPLACE(@Html,'<TABLE>' ,'<TABLE  class="gridtable">' + @Header)

      DROP TABLE ##columns

END;
GO

Code that calls uspConvertTableToHtml Stored Procedure 

		CREATE TABLE ##Results
		(
			OrderId					BIGINT,
			CustomerId			BIGINT,
			OrderTotal				VARCHAR(50),
			CreatedDate				VARCHAR(50),
			CreatedUser				VARCHAR(50)
		);

		INSERT INTO ##Results
		SELECT 
			TOP 10 
			ord.OrderId,
			ord.CustomerId,
			FORMAT(ord.OrderTotal, 'C'),
			FORMAT(ord.CreatedDate,'yyyy-MM-dd HH:mm'),
			ord.CreatedUser				
		FROM 
			tblOrder ord 
		ORDER BY 
			OrderId DESC

		SELECT @count = COUNT(*) FROM ##Results;

		IF(@count > 0)
		BEGIN
	
			SET @sql = 'SELECT * FROM ##Results ';

			EXECUTE dbo.uspConvertTableToHtml @sql,@tableHTML OUTPUT;

		
			SET @subject_line = 'Alert: Latest Order as of ' + FORMAT(GETDATE(),'yyyy-MM-dd HH:mm')

			EXEC msdb.dbo.sp_send_dbmail 
			@recipients= 'me@test.com',
			@profile_name ='Default Profile',
			@subject = @subject_line,
			@body=@tableHTML,
			@body_format = 'HTML'

		END
		DROP TABLE ##Results;
Last modified: March 18, 2021

Author

Comments

Write a Reply or Comment