t-sql help!

Question

t-sql help!

Mh-397891

SSCertifiable

Points: 6704
More actions
December 6, 2014 at 5:30 am

#293145

I have the following three columns along with the data in table1
col1 col2 col3
Order1 AA 100
Order2 BB 200
Order3 CC 700
I want email sent with the above data in html table format and only if col3 values are less than 500, I want it's corresponding row highlighted in red color. Any help appreciated.
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

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

pietlinden SSC Guru Points: 63428 More actions · Answer 1

First things first - we need to understand what you really mean by this:

I want email sent with the above data in html table format and only if col3 values are less than 500, I want it's corresponding row highlighted in red color.

Could you give an example of what you want (even if it's some HTML)?

E-mail it only if the values are less than 500, or

highlight only the rows where the col3 value is less than 500?

Mh-397891 SSCertifiable Points: 6704 More actions · Answer 2

Yes, highlight only the rows if the values if col3 values are less than 500.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

Here is a very simple way of constructing a html table in T-SQL with the background definition you asked for

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @HTMLSTR VARCHAR(MAX) = '';

DECLARE @SAMPLE TABLE

(

col1 VARCHAR(20) NOT NULL

,col2 CHAR(2) NOT NULL

,col3 INT NOT NULL

);

INSERT INTO @SAMPLE(col1,col2,col3)

VALUES ('Order1','AA',100)

,('Order2','BB',200)

,('Order3','CC',700);

SELECT @HTMLSTR = '<TABLE BORDER="0">' +

(SELECT

'<TR><TD bgcolor="'+ CASE WHEN S.col3 < 500 THEN '#FF0000' ELSE '#FFFFFF' END +'">' + S.col1 + '</TD>'

+ '<TD bgcolor="'+ CASE WHEN S.col3 < 500 THEN '#FF0000' ELSE '#FFFFFF' END +'">' + S.col2 + '</TD>'

+ '<TD bgcolor="'+ CASE WHEN S.col3 < 500 THEN '#FF0000' ELSE '#FFFFFF' END +'">' + CAST(S.col3 AS VARCHAR(9)) + '</TD></TR>'

FROM @SAMPLE S

FOR XML PATH('TR'),TYPE).value('.[1]','VARCHAR(MAX)')

+ '</TABLE>';

SELECT @HTMLSTR;

Results

<TABLE BORDER="0">

<TR><TD bgcolor="#FF0000">Order1</TD><TD bgcolor="#FF0000">AA</TD><TD bgcolor="#FF0000">100</TD></TR>

<TR><TD bgcolor="#FF0000">Order2</TD><TD bgcolor="#FF0000">BB</TD><TD bgcolor="#FF0000">200</TD></TR>

<TR><TD bgcolor="#FFFFFF">Order3</TD><TD bgcolor="#FFFFFF">CC</TD><TD bgcolor="#FFFFFF">700</TD></TR>

</TABLE>

Edit: fixed the output

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 4

It seems like you might want to consider doing this with a reporting tool rather straight out of sql server.

Don Simpson

I'm not sure about Heisenberg.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 5

DonlSimpson (12/8/2014)
It seems like you might want to consider doing this with a reporting tool rather straight out of sql server.

+1

😎