0

So I have this code below that uses a stored procedure to send an html formatted email. I got it from here:

https://stackoverflow.com/a/29708178/1452574

Now, the code runs great and I have a nice html table, but the problem is that my ROUND is not holding up. If I run the code in SSMS, it clearly works, rounds to 2 decimal places, but when I get the email, the number column looks like this:

enter image description here

I mean, I guess it's rounding, but I don't want to see all those extra zeroes or that plus sign at the end. I've tried casting and formatting to a million different data types, I can't seem to get a nice number with 2 decimal places. I either get a whole number (if I cast to say Numeric), or the mess you see in the above screenshot. I tried using FORMAT to N2, but got this error:

Conversion failed when converting the nvarchar value '3.39' to data type int.

Code below, it's not the whole thing, but the first part is just testing for a rowcount, so I don't think it's relevant:

EXEC spQueryToHtmlTable @html = @html OUTPUT,  
@query = 
N'SELECT
    e.EmplName,
    CASE
        WHEN SUM(t.ManHrs) IS NULL THEN 0
        ELSE ROUND(SUM(t.ManHrs), 2)
    END AS [Hrs Logged]
FROM EmplCode e 
    LEFT JOIN TimeTicketDet t ON e.EmplCode = t.EmplCode 
    AND CAST(t.TicketDate AS DATE) = CAST(GETDATE() AS DATE) 
    AND t.WorkCntr <> 50
WHERE e.DeptNum LIKE ''PROD %''
    AND e.Active = ''Y''
GROUP BY e.EmplName
HAVING ROUND(SUM(t.ManHrs), 2) < 6
    OR ROUND(SUM(t.ManHrs), 2) IS NULL';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Email System',
    @recipients = 'sample@gmail.com',
    @body = @html,
    @body_format = 'HTML',
    @query_no_truncate = 1,
    @attach_query_result_as_file = 0,
    @execute_query_database = 'CompanyDB';
user1452574
  • 485
  • 1
  • 6
  • 15
  • 2
    One thing to note about `ROUND()` is that it keeps the original scale (decimal places) of whatever you put in. You might want to cast the result of the sum to decimal (e.g. (5, 2)) instead of using `ROUND()`. – ZLK Apr 17 '18 at 23:16
  • use `CAST(t.ManHrs AS DECIMAL(18,2) )` – iSR5 Apr 17 '18 at 23:18
  • Thanks, that DECIMAL thing worked perfectly – user1452574 Apr 17 '18 at 23:27
  • You might have a look at [this answer](https://stackoverflow.com/a/39487565/5089204). This function will transfer any `SELECT` into an `XHTML` table. The approach you are using is concatenating `HTML` tags. Very dangerous, if your values might carry forbidden characters. Your `EmplName` won't include them assumably, but if you use this approach with a company's name *Do & Co* the whole approach would break with errors hardly to find. – Shnugo Apr 18 '18 at 08:53
  • Sorry, I don't really understand your answer about XHTML. The stored procedure I use has worked great for me and is very easy for me to understand. Your answer looks a bit too advanced for me. Also, I don't know what forbidden characters are, so I'm not quite sure what problems I may run into – user1452574 Apr 20 '18 at 18:33

0 Answers0