This script will create a stored procedure that can be used to send dbMail with query results in an html table. For ease of use I've broken the query parts out into separate parameters. The list of fields being returned should be delimited using the pipe character |.
There are other sources that explain how to include query results as an html table. The focus of this script is to create a stored procedure that is easy to use. The stored procedure is also set up to not send emails if no results are returned from the query. Note that all objects must be fully qualified (database.schema.object) and parts of the query that are not needed are optional.
A sample call would be:
@qSELECT = N'SELECT TOP 100',
@fieldlist = N' COUNT(tC.Client_ID) AS ClientCount|LEFT(tC.Client_FullName,2) AS ShortClientName|tB.AssociatesId',
@qFROM = N'FROM database.dbo.Clients tC
INNER JOIN database.dbo.Associates tB ON tB.AssociatesId = tC.AssociatesId',
@qWHERE = N'WHERE tC.Deleted =0',
@qGroupBy = N'GROUP BY LEFT(tC.Client_FullName,2), tB.AssociatesId',
--@qHaving = N'HAVING COUNT(tC.Client_ID)>2',
@qOrderBy = N'ORDER BY tB.AssociatesId',
@recipients = N'david.gugg@SQLServerCentral.com',
@subject = N'email test',
@Title = N'Query Results'