Feb 14, 2012

Sql query to get table data output in HTML format in sql server

Sql query to get table data output in HTML format in sql server

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&amp;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:


14 comments:

  1. Anonymous9/06/2012

    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.

    ReplyDelete
  2. Thanks a lot. Fantastic work. Saved a lot of time for me !!!

    ReplyDelete
  3. Very neat code! Had little problem with non varchar columns. Other than that, just worked!!!

    Thanks a lot.
    Ranjithkumar, Yella

    ReplyDelete
  4. This is awesome.

    ReplyDelete
  5. Anonymous9/11/2013

    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?

    ReplyDelete
  6. 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.

    ReplyDelete
    Replies
    1. This issue has fixed!!

      Delete
    2. 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.

      Delete
    3. Anonymous12/11/2013

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

      Delete
    4. 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.

      Delete
    5. 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 +''''

      Delete
  7. Anonymous7/17/2014

    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, '

    ReplyDelete
  8. Anonymous3/16/2017

    The html i am getting is trimmed considerably. Any suggestions.

    ReplyDelete