Friday, January 9, 2015

MSSQL - Run SQL and Email Result as HTML


USE AdventureWorks2012

DECLARE @xml NVARCHAR(MAX), @body NVARCHAR(MAX)
SET @xml = CAST((SELECT
                     ISNULL(PersonType, '') 'td',''
                    ,ISNULL(Title, '') 'td',''
                    ,ISNULL(FirstName, '') 'td',''
                    ,ISNULL(LastName, '') 'td'
                FROM Person.Person
                FOR XML PATH('tr'), ELEMENTS) AS NVARCHAR(MAX))


IF @xml IS NOT NULL
BEGIN   
    SET @body ='<html><body><H3>Header</H3>
                <table border=1>
                    <tr>
                        <th>Type</th>
                        <th>Title</th>
                        <th>FirstName</th>
                        <th>LastName</th>
                    </tr>' 
    SET @body = @body + @xml + '</table><br><a href="http://yahoo.com">This is a link</a></body></html>'

    EXEC DataWarehouse.dbo.sp_SQLNotify 'FromEmail', 'ToEmail', 'Subject', @body, ''
END
　
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)