Returning query to an HTML table alert email with TSQL dbmail

Question

I have a stored procedure that works like a charm, and i am really happy with it. In order to be perfect I just need it to do a little thing more. it returns nothing, if no parameter is met, or it just says "found one", if the select has somehing to return. Now, the rough part. This database has some dossiers that have a deadline. When that deadline has arrived i receive an email saying "hey, one of this dossiers has reached the expiration date". what i need is to get some data from inside that specific dossier, number, reference, name,... how can i reach this with the procedure, in order to avoid checking all possible dossiers to see which one terminated.

I can't get myself in to solving this, sorry to say :( Had kind of the same procedure on an earlier sql-server version, it returned that data and i don't remember doing anything for that to happen

CREATE PROCEDURE dbo.DossierEmailSend
 AS
 DECLARE @rows int;
 DECLARE @message varchar(1000);
 SET @rows = (SELECT COUNT(*)  
            FROM bo
            WHERE nmdos LIKE '%preço%'
            AND datafinal = DATEADD(day, -1, CONVERT(date, GETDATE()))
            )
 SET NOCOUNT ON 
 SET @message = '<HTML>As at ' + CONVERT(char(19),GETDATE(),120) + '<BR><BR>ONE UP'

 IF @rows > 0 
 BEGIN

 EXEC dbo.uspSendEmail 'aaaaaaa', 'aaaaaaaa@aaaa', @message, NULL, 'CC EMAIL 1'
 SET NOCOUNT OFF

 END

i just get the message saying one of the dossiers reached the deadline, need to know which one

You need to make the `SELECT` return a list of values you want to see and send those values as the content of the email. You can use STRING_AGG if on 2017+ or FOR XML on lower versions. Tons of answers here you can check. — EzLo, Feb 07 '19 at 14:04
Do you mean you would like the dossier name and date returned in the email body for the ones that have met the criteria? — jimmy8ball, Feb 07 '19 at 14:16

jimmy8ball · Accepted Answer · 2019-02-11T17:30:20.623

Because the requirement is to pass multiple rows to the body of the email, it would be best served in a tabular format.

This can be achieved by including in the email body, an HTML table of dossiers that meet the criteria.

Simply replace the column name's with the correct name's from table bo.

Let's break down the elements individually:

CREATE PROCEDURE dbo.DossierEmailSend
AS

Create a holding table for the dossiers that met the criteria

DECLARE @dossiers TABLE (col1 varchar(100), col2 varchar(100), col3 varchar(100), col4 varchar(100));

Insert the dossiers that met the criteria

INSERT INTO @dossiers
SELECT col1, col2, col3, convert(varchar,col4) col4  
    FROM bo
    WHERE nmdos LIKE '%preço%'
    AND datafinal = DATEADD(day, -1, CONVERT(date, GETDATE()))

Create a row check to determine whether to send the email or not

DECLARE @rows int;
SET @rows = (SELECT COUNT(*) FROM @dossiers)

Check if any dossiers met the criteria

IF @rows > 0 
BEGIN

Set the body elements

DECLARE @message varchar(1000);
-- declare the xml data to pass to the HTML body
DECLARE @xml NVARCHAR(MAX);
-- body will hold the HTML formatted table in the email
DECLARE @body NVARCHAR(MAX);

Create the columns that will hold each row of data as xml

SET @xml = CAST(( SELECT col1 AS 'td','',col2 AS 'td','', col3 AS 'td','', col4 AS 'td'
FROM @dossiers
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

Set the HTML for the body

SET @body ='<html><body><H3>Dossier Info</H3>
<table border = 1> 
<tr>
<th> col1 </th> <th> col2 </th> <th> col3 </th> <th> col4 </th></tr>'

Stitch everything together, appending the HTML table

SET @body = @body + @xml +'</table></body></html>'

SET NOCOUNT ON

Send the email and append the data table to the body

EXEC dbo.uspSendEmail 'Dossiers FOund', 'aaaaaaaa@aaaa', @body, NULL, 'CC EMAIL 1'
SET NOCOUNT OFF

END

The finished solution should look something like the below:

CREATE PROCEDURE dbo.DossierEmailSend
AS
 --Create a holding table for the dossiers that met the criteria

DECLARE @dossiers TABLE (col1 varchar(100), col2 varchar(100), col3 varchar(100), col4 varchar(100));

 --Insert the dossiers that met the criteria

INSERT INTO @dossiers
SELECT col1, col2, col3, convert(varchar,col4) col4  
    FROM bo
    WHERE nmdos LIKE '%preço%'
    AND datafinal = DATEADD(day, -1, CONVERT(date, GETDATE()))

 --Create a row check to determine whether to send the email or not

DECLARE @rows int;
SET @rows = (SELECT COUNT(*) FROM @dossiers)

 --Check if any dossiers met the criteria

IF @rows > 0 
BEGIN

 --Set the body elements

DECLARE @message varchar(1000);
-- declare the xml data to pass to the HTML body
DECLARE @xml NVARCHAR(MAX);
-- body will hold the HTML formatted table in the email
DECLARE @body NVARCHAR(MAX);

 --Create the columns that will hold each row of data as xml

SET @xml = CAST(( SELECT col1 AS 'td','',col2 AS 'td','', col3 AS 'td','', col4 AS 'td'
FROM @dossiers
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

 --Set the HTML for the body

SET @body ='<html><body><H3>Dossier Info</H3>
<table border = 1> 
<tr>
<th> col1 </th> <th> col2 </th> <th> col3 </th> <th> col4 </th></tr>'

 --Stitch everything together, appending the HTML table

SET @body = @body + @xml +'</table></body></html>'

SET NOCOUNT ON 

 --Send the email and append the data table to the body

EXEC dbo.uspSendEmail 'Dossiers FOund', 'aaaaaaaa@aaaa', @body, NULL, 'CC EMAIL 1'
SET NOCOUNT OFF

END

this is 98% purrrfect @jimmy8ball ! just got this litlle thingie i can't understand, when executing this got this error: Msg 116, Level 16, State 1, Procedure DossierEmailSend, Line 25 [Batch Start Line 0] Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. — questionador, Feb 07 '19 at 16:45
well @jimmy8ball, it's going to be kind of a problem, just because the 'bo' table has literally 183 columns, don't really know how to do that — questionador, Feb 08 '19 at 09:19
ok, this an example of some data, and these are the columns that i need to get the data on the e-mails. obrano dataobra datafinal no nome 27517 10/11/09 0:00 12/11/09 0:00 517661 CHICO & MODESTO, LDA 27518 10/11/09 0:00 12/11/09 0:00 517668 CHICO & MODESTO, LDA 27614 17/11/09 0:00 19/11/09 0:00 517668 CHICO & MODESTO, LDA 1 22/5/14 0:00 31/3/16 0:00 517747 QUADROS & FESTAS,LDA I hope it´s understandable — questionador, Feb 08 '19 at 10:24
yep @jimmy8ball , i was so commited trying to copy the table data I only saw after, sorry. one of the columns i need to get the data from the table is date, i'm getting an error saying: Msg 206, Level 16, State 2, Procedure DossierEmailSend, Line 7 [Batch Start Line 0] Operand type clash: numeric is incompatible with date line 7 is the INSERT INTO line — questionador, Feb 08 '19 at 10:32
Updated - now converting your date to varchar - assumes your date column is col4 — jimmy8ball, Feb 08 '19 at 10:35

StepUp · Answer 2 · 2019-02-07T15:01:58.430

0

If this code returns COUNT of dossiers:

SET @rows = (SELECT COUNT(*)  
        FROM bo
        WHERE nmdos LIKE '%preço%'
        AND datafinal = DATEADD(day, -1, CONVERT(date, GETDATE()))
        )

Then just write the all dossiers:

...
DECLARE @DossierNames varchar(MAX);
SELECT @DossierNames = STUFF( (
        SELECT ',' + dossierName
        FROM bo
        WHERE nmdos LIKE '%preço%'
        AND datafinal = DATEADD(day, -1, CONVERT(date, GETDATE()))
        FOR XML PATH('')),
        1, 1,'')

IF @rows IS NOT NULL 
    SET @rows = CONCAT(@rows, ' ', @DossierNames)

...

edited Feb 07 '19 at 15:01

answered Feb 07 '19 at 14:11

StepUp

36,391
15
88
148

when the procedure runs, if it returns zero than nothing happens. if it returns 1 or 2 or 3, i get an email saying just that, "found dossier". i need it to return data inside that dossier, namely the name and the number. those dossiers as i call them, are values inside a table, of course. – questionador Feb 07 '19 at 14:16

Returning query to an HTML table alert email with TSQL dbmail

2 Answers2