HTML through SQL query + Help needed to add more HTML formating

Question

Post reply

HTML through SQL query + Help needed to add more HTML formating

Suresh Arumugam

Hall of Fame

Points: 3370
More actions
November 2, 2012 at 7:28 am

#266509

Hi All,
I have recently changed the text file attachment email to HTML format attachment thinking of improving the look and feel (!!??). Client liked it and now they wanted me to include different colors for alternate rows and to have text alignments, table border, header styles etc which i am not sure how to can implement.
Below is the sample query used to generate HTML. Please help me with your thoughts.
SET @outputHTML = @outputHTML +
N'<table border="1">' +
N'<tr><th>Qty</th><th>SKU</th><th>Barcode</th><th>Size</th><th>Description</th></tr>' +
CAST ( ( SELECT
= SUM(OD.Quantity_Ordered) , '',
td = OD.Sell_Id , '',
td = '' , '',
td = IsNull(IM.PICKSIZE_CODE,'') , '',
td = OD.Product_Name, ''
FROM ORDER_DETAIL (nolock) OD
INNER JOIN ORDER_HEADER (nolock) OH ON OH.ORDER_NUMBER = OD.ORDER_NUMBER
AND OH.ORDER_DATE_TIME = OD.ORDER_DATE_TIME
INNER JOIN SHIFT_ORDER (nolock) SO ON SO.ORDER_NUMBER = OH.ORDER_NUMBER
AND SO.ORDER_DATE_TIME = OH.ORDER_DATE_TIME
INNER JOIN SHIFT_INDICATOR (nolock) SI ON SI.SHIFT_ID = SO.SHIFT_ID
LEFT JOIN ITEM_MASTER (nolock) IM ON IM.SELL_ID = OD.SELL_ID
WHERE OH.ORDER_STATUS = 'M' AND SI.SHIFT_ID = @SHIFT_ID AND IsNull(IM.CODE,'') = @Flag
GROUP BY OD.Sell_Id,IM.PICKSIZE_CODE,OD.Product_Name
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>';
Thanks again,
Suresh
Regards,
Suresh Arumugam

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

These will be good places to start

http://www.sqlservercentral.com/blogs/robert_davis/2010/06/15/Building-HTML-Emails-With-SQL-Server-and-XML/

http://jahaines.blogspot.co.uk/2010/04/t-sql-tuesday-005-creating-emailing.html

I use the second one for a number of reports in house.

Suresh Arumugam Hall of Fame Points: 3370 More actions · Answer 2

anthony.green (11/2/2012)
These will be good places to start
http://www.sqlservercentral.com/blogs/robert_davis/2010/06/15/Building-HTML-Emails-With-SQL-Server-and-XML/
http://jahaines.blogspot.co.uk/2010/04/t-sql-tuesday-005-creating-emailing.html
I use the second one for a number of reports in house.

Thanks for the quick response Anthony. I am changing my queries based in the links you provided and getting some hope:-).

many thanks again.

Regards,
Suresh Arumugam