Format query output into an html table

Question

I need the sql query result sent by e-mail to be in readable form. A change to html would allow to create results in the table. I need help implementing html in the code below.

USE msdb
go

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON

IF EXISTS (select Kod, Nazwa from DATABASE.dbo.Towar where Kod NOT LIKE '%?%' and AsId IN (205, 304, 289, 321, 306, 217, 261) and Aktywny = 1)
BEGIN
    EXEC sp_send_dbmail @profile_name='PROFILE',
        @recipients='myadres@email.com',
        @query_result_header=0,
        @attach_query_result_as_file=0,
        @query="select Kod, Nazwa from DATABASE.dbo.Towar where Kod NOT LIKE '%?%' and AsId IN (205, 304, 289, 321, 306, 217, 261) and Aktywny=1 ORDER BY Kod",
        @body_format='text',
        @subject='warning';
END;

GO

The example contained in the 'MgSam' post Convert a SQL query result table to an HTML table for email actually solves the problem of convert to html and create table. However, I have no idea how to implement the 'if' condition.

According to the MgSam guidelines - the following code works - but if the query does not return data - the email is sent.

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON

DECLARE @html nvarchar(MAX);

EXEC spQueryToHtmlTable @html = @html OUTPUT,  @query = "select Kod, Nazwa from DATABASE.dbo.Towar where Kod NOT LIKE '%?%' and AsId IN (205, 304, 289, 321, 306, 217, 261) and Aktywny = 1", @orderBy = N'ORDER BY Kod';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name='PROFILE',
    @recipients='my@email.com',
    @subject = 'WARNING',
    @body = @html,
    @body_format = 'HTML',
    @query_no_truncate = 1,
    @attach_query_result_as_file = 0;

Possible duplicate of [Convert a SQL query result table to an HTML table for email](https://stackoverflow.com/questions/7070053/convert-a-sql-query-result-table-to-an-html-table-for-email) — Ryan Sparks, Mar 16 '19 at 19:33

score 0 · Answer 1 · answered Mar 18 '19 at 02:56

Well, IF statements in SQL Server are pretty straightforward, please take a look here: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/if-else-transact-sql

Based on your example:

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON

DECLARE @html nvarchar(MAX);

EXEC spQueryToHtmlTable @html = @html OUTPUT,  @query = "select Kod, Nazwa from DATABASE.dbo.Towar where Kod NOT LIKE '%?%' and AsId IN (205, 304, 289, 321, 306, 217, 261) and Aktywny = 1", @orderBy = N'ORDER BY Kod';

IF DATALENGTH(@html) > 0 
  EXEC msdb.dbo.sp_send_dbmail
    @profile_name='PROFILE',
    @recipients='my@email.com',
    @subject = 'WARNING',
    @body = @html,
    @body_format = 'HTML',
    @query_no_truncate = 1,
    @attach_query_result_as_file = 0;
ELSE PRINT 'No results, don't email';

NB: you might need to adjust the ">0" limit if the conversion to table injects certain wrappers for an empty result set -- I haven't checked the underlying sproc. E.g. if the @html results in " " with no data — Megadest, Mar 18 '19 at 03:01

score 0 · Answer 2 · answered Mar 19 '19 at 05:08

I used IF EXISTS. The code that works.

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON

DECLARE @html nvarchar(MAX);

IF EXISTS (select Kod, Nazwa from DATABASE.dbo.Towar where Kod NOT LIKE '%?%' and AsId IN (205, 304, 289, 321, 306, 217, 261) and Aktywny = 1)

BEGIN

EXEC spQueryToHtmlTable @html = @html OUTPUT,  @query = "select Kod, Nazwa from DATABASE.dbo.Towar where Kod NOT LIKE '%?%' and AsId IN (205, 304, 289, 321, 306, 217, 261) and Aktywny = 1", @orderBy = N'ORDER BY Kod';

 EXEC msdb.dbo.sp_send_dbmail
    @profile_name='PROFILE',
    @recipients='my@email.com',
    @subject = 'WARNING',
    @body = @html,
    @body_format = 'HTML',
    @query_no_truncate = 1,
    @attach_query_result_as_file = 0;
    END;

GO

Format query output into an html table

2 Answers2