How to display stored procedure output in html table format using sql server 2000

Question

Post reply

How to display stored procedure output in html table format using sql server 2000

raghuldrag

Hall of Fame

Points: 3160
More actions
March 16, 2014 at 10:50 pm

#290928

HI Friends,
i m creating one google map application using asp.net with c# i had done also
now that marker ll be shown from database (lat,long)
depends on the lat,long i wanna display customer,sales,total sales for each makers in html table format
How to do that?

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

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

mcx5000 Old Hand Points: 365 More actions · Answer 1

You could use this to produce simple html from a select statement:

declare @html nvarchar(max), @table nvarchar(max);

set @html =

N'<html><head><title>Doc Title Goes Here</title></head>' + CHAR(10)

+ N'<body style="font-family: Arial size:8pt">'

+ N'<h2>Heading Goes Here</h2>'

+ N'<table border="1" style="font-size:11px">'

+ N'<tr>'

+ N'<th >Column 1</th>'

+ N'<th >Column 2</th>'

+ N'<th >Column 3</th>'

+ N'</tr>' + CHAR(10);

select @table =

CONVERT(nvarchar(max),

(select

td = isnull(cast([Column 1] as varchar(100)), '')

,'' ,td = isnull(cast([Column 2] as varchar(100)), '')

,'' ,td = isnull(cast([Column 3] as varchar(100)), '')

from [Your Table]

where 1=1

FOR XML PATH(N'tr'), TYPE));

set @html = @html + @table + CHAR(10) + N'</table></body></html>';

select @html;

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

mcx5000 (3/17/2014)
You could use this to produce simple html from a select statement:
declare @html nvarchar(max), @table nvarchar(max);
set @html =
N'<html><head><title>Doc Title Goes Here</title></head>' + CHAR(10)
+ N'<body style="font-family: Arial size:8pt">'
+ N'<h2>Heading Goes Here</h2>'
+ N'<table border="1" style="font-size:11px">'
+ N'<tr>'
+ N'<th >Column 1</th>'
+ N'<th >Column 2</th>'
+ N'<th >Column 3</th>'
+ N'</tr>' + CHAR(10);
select @table =
CONVERT(nvarchar(max),
(select
td = isnull(cast([Column 1] as varchar(100)), '')
,'' ,td = isnull(cast([Column 2] as varchar(100)), '')
,'' ,td = isnull(cast([Column 3] as varchar(100)), '')
from [Your Table]
where 1=1
FOR XML PATH(N'tr'), TYPE));
set @html = @html + @table + CHAR(10) + N'</table></body></html>';
select @html;

Although that's excellent, it's not going to work for the OP. Look at the title of the post and see that the OP posted an SQL Server 2000 question in a 2008 forum.

--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)