Returning query to an HTML table alert email with TSQL dbmail

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

Solution

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