We can easily convert output of sql queries in html format. For
this we have to create a stored procedure ConvertTableToHtml
Stored Procedure:
CREATE PROCEDURE [dbo].[ConvertTableToHtml](
@SqlQuery AS
NVARCHAR(4000),
@FilePath AS
VARCHAR(100)
)
AS
DECLARE
@Html AS VARCHAR(MAX) = ''
DECLARE
@Command AS VARCHAR(8000) = ''
DECLARE
@Header AS NVARCHAR(MAX) = ''
DECLARE
@Column AS NVARCHAR(MAX) = ''
DECLARE
@Query AS NVARCHAR(MAX)
DECLARE
@Css AS VARCHAR(MAX) = '
<style
type="text/css">
table.gridtable {
font-family: verdana,arial,sans-serif;
font-size:11px;
color:#333333;
border-width: 1px;
border-color: #666666;
border-collapse: collapse;
}
table.gridtable th {
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #666666;
background-color: #dedede;
}
table.gridtable td {
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #666666;
background-color: #ffffff;
}
</style>
'
BEGIN
SET @Query
= 'SELECT * INTO
##columns FROM ( ' + @SqlQuery + ') Temp'
EXECUTE(@Query)
SELECT
@Column = @Column +
'ISNULL( CONVERT( VARCHAR(MAX),' + QUOTENAME(name ) + ',1) ,'' '')' +
' AS TD , '
--SELECT
@Column = @Column + QUOTENAME(name) + ' AS TD, '
FROM
tempdb.SYs.columns
WHERE object_id = OBJECT_ID('tempdb..##columns')
SET @Column = LEFT(@Column,LEN(@Column)-1)
SELECT
@Header = @Header +
'<TH>' + name + '</TH>'
FROM
tempdb.SYs.columns
WHERE object_id = OBJECT_ID('tempdb..##columns')
SET
@Header = '<TR>'
+ @Header
+ '</TR>'
SET
@Query = 'SET @Html = (SELECT ' + @Column + ' FROM ( ' + @SqlQuery
+ ') AS TR
FOR XML AUTO ,ROOT(''TABLE''),
ELEMENTS)'
EXECUTE SP_EXECUTESQL @Query,N'@Html VARCHAR(MAX) OUTPUT',@Html OUTPUT
SET @Html = @Css + REPLACE(@Html,'<TABLE>' ,'<TABLE
class="gridtable">' +
@Header)
DROP TABLE ##columns
SELECT
@Html AS #Html INTO
#HtmlTable
SET
@Command = 'bcp
"select * FROM #HtmlTable" queryout "' + @FilePath + '" -c -t"," -r"\n" -T'
exec master..xp_cmdshell @Command
END
To execute:
DECLARE @Html AS VARCHAR(MAX)
EXECUTE
ConvertTableToHtml '
SELECT TOP(10) * FROM Departments ',@Html
OUTPUT
SELECT @Html
Sample
output:
<style
type="text/css">
table.gridtable
{
font-family: verdana,arial,sans-serif;
font-size: 11px;
color: #333333;
border-width: 1px;
border-color: #666666;
border-collapse: collapse;
}
table.gridtable th
{
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #666666;
background-color: #dedede;
}
table.gridtable td
{
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #666666;
background-color: #ffffff;
}
</style>
<table class="gridtable">
<tr>
<th>
deptid
</th>
<th>
deptname
</th>
<th>
deptmgrid
</th>
</tr>
<tr>
<td>
1
</td>
<td>
HR
</td>
<td>
2
</td>
</tr>
<tr>
<td>
2
</td>
<td>
Marketing
</td>
<td>
7
</td>
</tr>
<tr>
<td>
3
</td>
<td>
Finance
</td>
<td>
8
</td>
</tr>
<tr>
<td>
4
</td>
<td>
R&D
</td>
<td>
9
</td>
</tr>
<tr>
<td>
5
</td>
<td>
Training
</td>
<td>
4
</td>
</tr>
<tr>
<td>
6
</td>
<td>
Gardening
</td>
<td>
0
</td>
</tr>
</table>
Screenshot:
16 comments:
Thank you! I have been searching for quite a while. Nice solution.
I am using this to email html results. I did tweak your script to return 1 if there are no results. That way, I do not send blank emails.
Thanks a lot. Fantastic work. Saved a lot of time for me !!!
Very neat code! Had little problem with non varchar columns. Other than that, just worked!!!
Thanks a lot.
Ranjithkumar, Yella
This is awesome.
I am getting compilation error for using @ symbol while creating procedure using the above script..can anyone help me why i am facing this problem?
I agree with Ranjith Kumar Yella - have also problems with non-varchar columns. It say:
(4 row(s) affected)
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
...
This issue has fixed!!
Ritesh, I am facing the same issue described by Marek. I need to deliver production issue by tomorrow. Kindly let me know where I am going wrong. I have used the same code as given in your blog.
Hi I am facing the same issue, please help me. I am facing the same problem on production and need the solution by tomorrow. I used the same code ... and facing the same above error. Kindly let me know where I am going wrong ..
Can you give me schema of your table with some sample data for which are your getting error. Also give me ur exact error message. Since in my case I'm not getting such error.
Hi Ritesh, I have fixed the issue ...
For all, who is facing this problem the solution is - when you pass the query using @sql parameter .. make sure that you define all your attributes as VARCHAR(). For me some of the attributes were number so I convert them like this ...
Set @sqlx = 'SELECT CAST(Territory as VARCHAR(20)) as Territory,
CAST(Grp as VARCHAR(20)) as Grp,
CAST(MajRevNov as VARCHAR(20)) as [NOV_Major_Rev],
CAST(MajRevDec as VARCHAR(20)) as [DEC_Major_Rev],
CAST(Goal as VARCHAR(20)) as [Actual_Percentage],
CAST(BonusMajorRevenue as VARCHAR(20)) as [Actual_Bonus_Revenue],
CAST(ProjBnsRev as VARCHAR(40)) as [Projected_Bonus_Revenue] FROM #fGP where territory = ''' + @idmTerr +''''
Had to change this line to avoid the "Error converting data type varchar to numeric." Error:
SELECT @Column = @Column + 'ISNULL(CAST(' + QUOTENAME(name) +' AS NVARCHAR(MAX)) ,'' '')' + ' AS TD, '
The html i am getting is trimmed considerably. Any suggestions.
İt's not working, error below
"The formal parameter "@FilePath" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output."
Can i get the same in mYsql? Please update
Post a Comment