Generating HTML TABLE from a select query

Question

Generating HTML TABLE from a select query

descentflower

Ten Centuries

Points: 1227
More actions
February 4, 2010 at 4:31 am

#142147

Hi,
I have a simple select output that i need to send to a user through email from sql server 2005.
Hence i need to convert my sql select output into htmltable. how would i ?
[Code]
select Name,EmailAddress,ExpiryDate from tblSubscribers
[/Code]
Expected Output
-----------------
<table><tr><td></td></tr></table> and so on...
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

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

wschampheleer SSCertifiable Points: 5504 More actions · Answer 1

I'm not really into HTML so there may be something wrong with the formatting, but this should be enough to get you there (I developed the sample on the AdventureWorks2008 database).

DECLARE @out VARCHAR(MAX) = '<table>' ;

SELECT @out = @out + '<tr><td>' + [AddressLine1] + '</td><td>' + [City] + '</td><td>' + [PostalCode] + '</td></tr>'

FROM [Person].[Address]

WHERE [AddressID] < 10

SELECT @out + '</table>'

Be careful if any of your data fields can be NULL -> it will make the entire result NULL. In that case you should wrap the column as follows:

ISNULL([AddressLine1],'')

You can also replace '' with a string like e.g. ISNULL([AddressLine1],'empty')

Regards,

Willem
http://wschampheleer.wordpress.com[/url]

Jeff Moden SSC Guru Points: 1003943 More actions · Answer 2

I'm not sure that it will do exactly what you want and I believe it's been deprecated in 2k5, but take a look at sp_MakeWebTask in Books Online.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

descentflower Ten Centuries Points: 1227 More actions · Answer 3

Thank you wschampheleer. Some minor changes done and it worked.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 4

Jeff Moden (2/4/2010)
I'm not sure that it will do exactly what you want and I believe it's been deprecated in 2k5, but take a look at sp_MakeWebTask in Books Online.

I am not sure which feature you talking about that is deprecated, but for sp_MakeWebTask BOL says

sp_makewebtask is retained for backward compatibility. New Web pages are more easily created using Microsoft SQL Server 2005 Reporting Services (SSRS).

To execute sp_makewebtask you must be a member of the sysadmin fixed server role.

This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.

--Ramesh

s_osborne2 SSCertifiable Points: 6726 More actions · Answer 5

Be careful if any of your data fields can be NULL -> it will make the entire result NULL. In that case you should wrap the column as follows:
ISNULL([AddressLine1],'')
You can also replace '' with a string like e.g. ISNULL([AddressLine1],'empty')

Rather than use ISNULL you could use this at the top of your script.

SET CONCAT_NULL_YIELDS_NULL OFF ;

It's easier than using ISNULL many times if you have lots of fields.

MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne

Jeff Moden SSC Guru Points: 1003943 More actions · Answer 6

Ramesh Saive (2/5/2010)
Jeff Moden (2/4/2010)
I'm not sure that it will do exactly what you want and I believe it's been deprecated in 2k5, but take a look at sp_MakeWebTask in Books Online.
I am not sure which feature you talking about that is deprecated, but for sp_MakeWebTask BOL says
sp_makewebtask is retained for backward compatibility. New Web pages are more easily created using Microsoft SQL Server 2005 Reporting Services (SSRS).
To execute sp_makewebtask you must be a member of the sysadmin fixed server role.
[font="Arial Black"]This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. [/font]

The same one you quoted... "deprecated" means "still available but going away soon". 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 7

Jeff,

Nobody has mentioned about the deprecated procedure expect you, so I was little confused, but now everything is fine.

--Ramesh