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: