Select Statement to HTML Table

Introduction

If you need a udp for making an HTML table (ex notification for mail message), here there is my solution without CLR.

You can override the parameters or leave the default style.

Procedure

Here is the code of user defined procedure:

    ALTER PROCEDURE [dbo].[udp_QueryToHtmlTable] 
    @SelectStatment NVARCHAR(MAX)
    ,@SortingCondition NVARCHAR(MAX) =null
    ,@RowLimit INT = NULL
    ,@TableStyle VARCHAR(MAX) =NULL
    ,@HeaderStyle VARCHAR(MAX) =NULL
    ,@RowStyle VARCHAR(MAX) =NULL
    ,@OddRowStyle VARCHAR(MAX) =NULL
    ,@MessageForTopRow NVARCHAR(MAX)=null
    ,@HtmlResult VARCHAR(MAX) OUTPUT
    AS
    BEGIN
    
    DECLARE @TotalRow            INT
    DECLARE @TSQL                NVARCHAR(MAX)
    DECLARE @HtmlLimitMessage    VARCHAR(MAX)
    DECLARE @HeaderColumnList    VARCHAR(MAX)
    SET @HtmlLimitMessage=''

    IF  isNULL(@RowLimit,'0')<=0    SET @RowLimit=10
    IF  isNULL(@TableStyle,'')=''    _
    SET @TableStyle='font-family: ''''Helvetica Neue'''', _
    Helvetica, Arial; font-size: 14px; line-height: 20px; font-weight: 400; _
    color: #3b3b3b; -webkit-font-smoothing: antialiased; font-smoothing: antialiased;margin: 0 0 40px 0; _
    width: 100 %; box-shadow: 0 1px 3px rgba(0,0,0,0.2);border-collapse: collapse;'
    IF  isNULL(@HeaderStyle,'')=''    _
    SET @HeaderStyle='font-weight: 900; color: #ffffff; background: #434a59;'
    IF  isNULL(@RowStyle,'')=''        SET @RowStyle='background: #f6f6f6;'
    IF  @OddRowStyle is NULL        SET @OddRowStyle= 'background: #e9e9e9;'
    IF  isNULL(@MessageForTopRow,'')='' _
    SET @MessageForTopRow= 'Here the top $ROWLIMIT$  of $TOTALROW$' 

    SET @TSQL=N' set @Count= (SELECT count(1) from ( '  + @SelectStatment +  ' ) AS t ) '

    EXEC sp_executesql  @TSQL,N'@Count int out' , @Count=@TotalRow OUT

    IF (@TotalRow> @RowLimit)
        BEGIN
            SET  @HtmlLimitMessage ='<p>' +replace( replace_
            ( @MessageForTopRow,'$ROWLIMIT$', @RowLimit),'$TOTALROW$', @TotalRow) + '</p>' 
        END

    BEGIN TRY
        DROP TABLE #htmlTempTable
    END TRY
    BEGIN CATCH END CATCH

    SET @TSQL ='
         
                SELECT TOP(1)  *  
                INTO #HtmlTempTable 
                FROM  ( '  + @SelectStatment +  ' ) AS t          
            
                SET @HeaderColumnList =cASt((SELECT  Name AS [td] 
                FROM tempdb.sys.columns 
                WHERE object_id=OBJECT_ID(''tempdb.dbo.#HtmlTempTable'') 
                ORDER BY column_id ASC FOR XML PATH('''')) AS VARCHAR(MAX)) 

            SET    @HtmlResult =''<table' + (CASE WHEN @TableStyle='' _
            THEN '' ELSE ' style="' + @TableStyle + '"' END) + '>'' 
            SET    @HtmlResult =     @HtmlResult + ''<tr' + _
            (CASE WHEN @HeaderStyle='' THEN '' ELSE ' style="' + _
            @HeaderStyle + '"' END) + '>'' 
            SET    @HtmlResult =     @HtmlResult +  @HeaderColumnList + ''</tr>''
                '
    exec sp_executesql  @TSQL ,N'@HtmlResult varchar(MAX) OUT,@HeaderColumnList varchar(MAX) OUT' , _
    @HtmlResult=@HtmlResult OUT , @HeaderColumnList=@HeaderColumnList OUT  
 
     SET @TSQL ='
                DECLARE @index INT 
                DECLARE @limit INT            

                SELECT TOP( ' + cast( @RowLimit AS varchar) + ' )  *  ,
                ROW_NUMBER() OVER(ORDER BY ' + (CASE WHEN  isNULL(@SortingCondition,'')='' 
                                                THEN  '[' +replace(replace( replace_
                                                (@HeaderColumnList,'</td><td>','],['),_
                                                '<td>',''),'</td>','') +']'
                                                ELSE  @SortingCondition END )
                                + ') AS [$rn]
                INTO #HtmlTempTable 
                FROM  ( '  + @SelectStatment +  ' ) AS t 
            
                ALTER TABLE #HtmlTempTable 
                ADD [$HtmlResult] VARCHAR(MAX)
             
                SET @limit = (SELECT MAX( [$rn]) FROM  #HtmlTempTable )
                SET @index =1
            
                WHILE @index<=@limit 
                BEGIN
                    IF @index%2=0 
                        BEGIN
                            SET @HtmlResult=@HtmlResult + ''<tr' + (CASE WHEN @RowStyle + _
                            @OddRowStyle='' THEN '' ELSE ' style="' + _
                            @RowStyle + @OddRowStyle + '"' END) + '>'' 
                        END
                    ELSE
                        BEGIN
                            SET @HtmlResult=@HtmlResult + ''<tr' + _
                            (CASE WHEN @RowStyle='' THEN '' ELSE ' style="' + _
                            @RowStyle + '"' END) + '>'' 
                        END

                    SET @HtmlResult=@HtmlResult+ cASt((SELECT isNULL([' + replace(replace_
                    (replace(@HeaderColumnList,'</td><td>','],'''') _
                    AS [td],isNULL(['),'<td>',''),'</td>','')  + '],'''') AS [td]
                                        FROM  #HtmlTempTable 
                                        WHERE  [$rn]=@index
                                        FOR XML RAW(''''),ELEMENTS,TYPE, BINARY BASE64 )  _
                                        AS VARCHAR(MAX))  + ''</tr>''
                    SET  @index+=1
                END
                SET @HtmlResult=@HtmlResult+ ''</table>'' 
 
                '         

    exec sp_executesql  @TSQL ,N'@HtmlResult VARCHAR(MAX) OUT ' , @HtmlResult=@HtmlResult OUT 
    
    set @HtmlResult = @HtmlLimitMessage + @HtmlResult
    print  @HtmlResult

    END

How to Run

And for running it, you can:

 DECLARE @HtmlTable varchar(MAX) 

 exec udp_QueryToHtmlTable @SELECTStatment='SELECT Employeeid,lastname,_
      firstName,homephone,title from [NORTHWND].[dbo].[Employees] ' 
 ,@SortingCondition ='Title desc' 
 ,@rowlimit=5
 ,@HtmlResult=  @HtmlTable output

 SELECT @HtmlTable

Automatically, the procedure takes the top 10 rows and if the query contains more rows, then @rowlimit parameters will set a paragraph before the table.

Output Sample

Here are the top 5 of 9:

Employeeid	lastname	firstName	homephone	title
2	Fuller	Andrew	(206) 555-9482	Vice President, Sales
3	Leverling	Janet	(206) 555-3412	Sales Representative
4	Peacock	Margaret	(206) 555-8122	Sales Representative
6	Suyama	Michael	(71) 555-7773	Sales Representative
7	King	Robert	(71) 555-5598	Sales Representative