• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 686
  • Last Modified:

T-SQL output to html

I would like to create a string from a simple select clause building an html table with the database rows...

I tried something like this but it didn't add multiple rows:

DECLARE @strHTML varchar(5000)

SET @strHTML = '<html><body><table>'

SELECT '<tr><td>'+NavPass+'</td><td>'+email+'</td></tr>' as strH FROM tblUser

SET @strHTML = @strHTML + '</table></body></html>'

can someone figure out how to have the return dataset encapsulated in an HTML table?
0
conrad2010
Asked:
conrad2010
1 Solution
 
Jon500Commented:
Hi,

You were close. Use this in your SELECT statement:

SELECT @strHTML = @strHTML + ...

Regards,
Jon500
0
 
Anthony PerkinsCommented:
You should be able to do this with a simple FOR XML clause, rather then resorting to concatenating strings and hoping that there are no special characters.
0
 
conrad2010Author Commented:
@Jon500

implemented yopur suggestion and got a NULL string back...

complete solution:
DECLARE @strHTML varchar(5000)

SET @strHTML = '<html><body><table>'

SELECT @strHTML = @strHTML + '<tr><td>'+field1+'</td><td>'+field2+'</td></tr>' FROM mytable

SET @strHTML = @strHTML + '</table></body></html>'

select @strHTML
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Aneesh RetnakaranDatabase AdministratorCommented:
DECLARE @strHTML varchar(5000)
SET @strHTML = ''

SELECT @strHTML + CAST ( ( SELECT      
                                                      td = NavPass, '',
                                                      td = email, ''
                                           FROM tblUser
                                           FOR XML PATH('tr'), TYPE
                                           ) AS Varchar(MAX) )+
                              N''            
0
 
conrad2010Author Commented:
super!
0
 
Jon500Commented:
Might you have a typo? I ran this code with no issue in my T-SQL SQL Server 2005...

Thanks,
Jon500
0
 
Jon500Commented:
Oops, forgot attachment.
DECLARE @strHTML varchar(5000)

SET @strHTML = '<html><body><table>'

SELECT @strHTML = @strHTML + '<tr><td></td><td>'+countryname+'</td></tr>' FROM dbo.BP_Accounts_CountryCodes

SET @strHTML = @strHTML + '</table></body></html>'

SELECT @strHTML

Open in new window

0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now